Help with multiple Joins!

Databases
1

So I have 3 tables and a simplified version of it is as described below:

Table 1 (Student)
student_id
student_name
school_id (foreigner key for table 2)

Table 2 (School)
school_id
school_name

Table 3 (Teachers)
teacher_id
school_id (foreigner key for table 2)
teacher_name
teacher_sequence (1, 2, 3, 4, etc)

I need to select the student_name, school_name, teacher_name, teacher_name2; where teacher_name is a record in table 3 where teacher_sequence=1 and teacher_name2 is a second record in table 3 where teacher_sequence=2.

I was able to accomplish this task with the following SQL query:

select student.student_name, school.school_name, teacher.teacher_name, teacher2.teacher_name from student, school LEFT JOIN teacher ON school .school _id=teacher.school_id, school as school2 LEFT JOIN teacher as teacher2 ON school2.school_id=teacher2.school_id where student.school_id=school.school_id and school.school_id=teacher.school_id and school.school_id=teacher2.school_id and student_id=‘100’ and teacher.teacher_sequence=1 and teacher2.teacher_sequence=2;

The problem that I am having is when a school only has one teacher I get an empty query! I need to return a student name and school name when the school has one or none teachers!

I hope I was clear.
Thanks in advance,

2

is this a homework assignment?

because it is seriously messed up

the question, i mean, not your attempted solution

3

Unfortunately is a real case scenario that I’ve stumbled into. Bad DB design I guess.

4

if you can redo the tables, might this help? (not sure the point of it though). surely the teachers need to be related to students?

Table 1 (school)
id
school_name

Table 2 (people)
id
name
school_id (foreigner key for table 2)
role (Teacher or student)
teacher_sequence (1, 2, 3, 4, etc)

then


SELECT
     p1.name
      s.school_name
     p2.teacher_name AS teach1
     p3.teacher_name AS teach2
   FROM people AS p1
INNER 
   JOIN school AS s
     ON s.id = p1.school_id
INNER 
   JOIN people AS p2
     ON p2.school_id = p1.school_id
    AND p2.role = 'Teacher'
    AND p2.teacher_sequence = 1
LEFT OUTER
   JOIN people AS p3
     ON p3.school_id = p1.school_id
    AND p3.role = 'Teacher'
    AND p3.teacher_sequence = 2
INNER 
   JOIN school AS s
      on s.id = p1.school_id
WHERE p1.id = 100