Join with relation to parent and grandparent

Question

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

Answer 1

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