LEFT OUTER JOIN with INNER JOIN

Question

I have a table of students. and a table of teachers.

SOME of the students (not all) will have a teacher assigned to them. this is controlled in a 3rd table, matching those students with their teachers, via the studentID and teacherID

what i need the SQL to do, is to LEFT OUTER JOIN onto the 3rd table, which is then INNER JOINED onto the teacher's table (because not all students will appear in the 3rd table, but any teacher that appears in the 3rd table WILL appear in the teachers table).

i am looking to get a result of all student names, and teacher's name, where they are assigned (and null if not).

what i have so far looks like this, and it basically operates as a full INNER JOIN, and does not give me students who do not have teachers assigned:

SELECT firstname, teacherlastName
FROM tblstudents 

left outer join [tblStudentRakazot] 
ON tblstudents.studentid = [tblStudentRakazot].studentID 

INNER JOIN tblteachers 
ON [tblStudentRakazot].teacherid = tblteachers.teacherID

can someone pls give me a pointer here? i tried with placing brackets, but that didn't see, to help.

thanks!

KekuSemau · Accepted Answer · 2013-11-22 10:28:28Z

1

You don't use an INNER JOIN but only another LEFT JOIN.

Think of tblStudents as your base. You want to get all of them, not filter anything out, and only attach optional info. With the first left join, you attach a first info
Student -> TeacherAssignment
The TeacherAssignment can be null or not null. Now you only attach another info - the teacher's full name pulled from tblTeachers.
Student -> TeacherAssignnent -> TeacherName
Do this with another LEFT JOIN. That attaches the info, where possible, i.e. where TeacherAssignment is not null. This ignores rows where TeacherAssignment is null anyway.

answered Nov 22, 2013 at 10:28

KekuSemau

6,8424 gold badges27 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sreejithsdev · Accepted Answer · 2013-11-22 10:38:12Z

1

SELECT firstname, teacherlastName
FROM tblstudents 
left outer join
( select * from
 [tblStudentRakazot] A INNER JOIN tblteachers B 
ON A.teacherid = B.teacherID)AS C
ON tblstudents.studentid = C.studentID

answered Nov 22, 2013 at 10:38

sreejithsdev

1,21012 silver badges26 bronze badges

1 Comment

kneidels Over a year ago

thanks for this, you answer was very helpful, because it enabled me to use a WHERE clause on the inner SELECT

GarethD · Accepted Answer · 2013-11-22 10:48:19Z

1

You could move your INNER JOIN to a subquery

SELECT  firstname, teacherlastName
FROM    tblstudents 
        LEFT OUTER JOIN
        (   SELECT  [tblStudentRakazot].studentID, tblTeachers.teacherlastName
            FROM    [tblStudentRakazot]
                    INNER JOIN tblteachers 
                        ON [tblStudentRakazot].teacherid = tblteachers.teacherID
        ) teachers
            ON tblstudents.studentid = teachers.studentID

Another option is to use a more complicated where clause.

SELECT  firstname, teacherlastName
FROM    tblstudents 
        LEFT JOIN [tblStudentRakazot] 
            ON tblstudents.studentid = [tblStudentRakazot].studentID 
        LEFT JOIN tblteachers 
            ON [tblStudentRakazot].teacherid = tblteachers.teacherID
WHERE   [tblStudentRakazot] IS NULL
OR      tblteachers.teacherID IS NOT NULL

However, SQL Server is pretty good at propogating predicates out of subqueries where it needs to, so I would favour the first approach for both readabilty and efficiency.

EDIT

I did not read the question properly, I thought you did not want records where the teacherID in tblStudentRakazot was NULL. If this is not an issue then you can simply use two LEFT JOINS, without the where clause as above:

SELECT  firstname, teacherlastName
FROM    tblstudents 
        LEFT JOIN [tblStudentRakazot] 
            ON tblstudents.studentid = [tblStudentRakazot].studentID 
        LEFT JOIN tblteachers 
            ON [tblStudentRakazot].teacherid = tblteachers.teacherID

edited Nov 22, 2013 at 10:48

answered Nov 22, 2013 at 10:19

GarethD

70k11 gold badges90 silver badges130 bronze badges

2 Comments

KekuSemau Over a year ago

I'm starting to wonder if I've misunderstood something: He just wants the Students and the assigned teachers or null for the teacher. There should be no need for any WHERE part at all in your second query...

GarethD Over a year ago

@KekuSemau I did not read the question properly, I thought the OP did not want records where the teacherID in tblStudentRakazot was NULL, which is why I thought the where clause was required, you are right, it isn't required.

vasin1987 · Accepted Answer · 2013-11-22 10:32:35Z

0

SELECT b.student_firstname, teacherlastName
FROM thirdtable a 
left join studenttbl b on a.studentid = b.studentid
left join teachertbl b on a.teacherid = b.teacherid

You can always use outer join if you are certain data in teacher table is unique. it will give same result as inner join.

answered Nov 22, 2013 at 10:32

vasin1987

2,0321 gold badge21 silver badges29 bronze badges

Comments

GriGrim · Accepted Answer · 2013-11-22 10:32:47Z

0

You may not use a subquery as in @GarethD sample.

SELECT firstname, teacherlastName
FROM tblstudents
LEFT OUTER JOIN [tblStudentRakazot]
    INNER JOIN tblteachers
        ON [tblStudentRakazot].teacherid = tblteachers.teacherID
    ON tblstudents.studentid = [tblStudentRakazot].studentID

But when looking more deeply execution plans of this query and query with subquery will likely be equivalent.

answered Nov 22, 2013 at 10:32

GriGrim

2,9411 gold badge25 silver badges38 bronze badges

Collectives™ on Stack Overflow

LEFT OUTER JOIN with INNER JOIN

5 Answers 5

Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related