CODEWITHSUNDEEP
sqlhierarchical-query
Pontus Ekengren
Pontus Ekengren

Reputation: 77

Join with relation to parent and grandparent

Given the relation:

Teacher -> Students -> Advisors

There can also be

Teacher -> Advisors

How can I write a join statement that shows me null values for the students where advisors have their FK pointing towards teachers and not students? Without writing two separate select statements?

As of now my query looks like this:

SELECT 
    t.teacherId, t.name,
    s.studentId, s.name,
    a.advisorId, a.name,
FROM
    teacher t 
JOIN
    student s ON t.teacherId = s.teacherId
JOIN 
    advisor ON s.studentId = a.personId

Edit: basically I want the result of the query below to be combined with the one above. But because the join in the middle (student) is missing for some FK's I expect those to be presented as null

SELECT 
    t.teacherId, t.name,
    a.advisorId, a.name,
FROM
    teacher t 
JOIN
    advisor ON s.teacherId = a.personId

Upvotes: 0

Views: 67

Answers (2)

Luuk
Luuk

Reputation: 14978

use UNION ALL:

SELECT t.teacherId, t.name,
       s.studentId, s.name,
       a.advisorId, a.name,
FROM teacher t 
JOIN student s
  ON t.teacherId = s.teacherId
JOIN advisor
  ON s.studentId = a.personId
UNION ALL
SELECT t.teacherId, t.name,
       null, null, 
       a.advisorId, a.name,
FROM teacher t 
JOIN advisor
  ON s.teacherId = a.personId

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271023

Use union all:

SELECT t.teacherId, t.name,
       s.studentId, s.name,
       a.advisorId, a.name
FROM teacher t JOIN
     student s
     ON t.teacherId = s.teacherId JOIN
     advisor a
     ON s.studentId = a.personId
UNION ALL
SELECT t.teacherId, t.name,
       NULL, NULL,
       a.advisorId, a.name
FROM teacher t JOIN
     advisor
     ON s.teacherId = a.personId

Upvotes: 1

Related Questions