SQL query using information from 4 tables (not all directly linked)

Question

I'm developing a simple classroom system, where teachers manage classes and their subjects.

I have 2 levels of access in my teachers table, assigned by an integer (1 = admin, 2 = user)... Meaning that the headteacher is the admin :)

A teacher (of level 1) can have have many classes and a class can have many teachers (so I have 'TeachersClasses' table). A class can have many subjects, and a teacher can have many subjects.

Basically, I'm attempting a query to display the admin teacher's (level 1) subjects. However, only teachers with a level of 2, are directly related to a subject, which is set by the admin user. The headteacher can view all of their subjects via the classroom, but I cannot get all of the subjects to be displayed on one page, instead I can only get the subjects to appear under a specific classroom right now...

This is what I have so far, which is returning nothing. (I'm guessing this may require an SQL clause more advanced that 'INNER JOIN' which is the only join type I am familiar with, and thought it would be enough!

  $query = "SELECT subjects.subjectid, subjects.subjectname, 
subjects.subjectdetails, classroom.classid, classroom.classname 
FROM subjects INNER JOIN classroom ON subjects.subjectid = classroom.classid 
INNER JOIN teacherclasses ON classroom.classid = teacherclasses.classid 
INNER JOIN teachers ON teacherclasses.teacherid = teachers.teacherid 
WHERE teachers.teacherid = '".intval( $_SESSION['SESS_TEACHERID'] )."'"; 

In order for all subjects related to the headteachers class to be displayed, I'm gathering that all of my tables will need to be called up here? Thanks for any help!

Example output:

subject name: maths // teacher: mr smith // classroom: DG99

x10 for all the subjects associated with the headteachers classrooms :)

Answer 1

This line looks like a bug:

INNER JOIN classroom ON subjects.subjectid = classroom.classid

You're not going to get any records when joining a subjectid with a classid.

Answer 2

Based on the names of the tables (having DDL to show you table structures would make this clearer), I'm guessing that the join between subjects and classroom is invalid. Did you really mean to join subjectid to classroom id?

Also, I'm not sure how the whole level1 and level2 thing is related (again, DDL might help readers understand), but the advance inner join you're talking about could be an outer join. If you have a teacher that you want to have included, even if they don't have a teacherclass record, you can do an outer join:

...teacherclasses ON classroom.classid = teacherclasses.classid right outer join teachers ON teacherclasses.teacherid = teachers.teacherid...

This will include all teachers records, even if their teacherid doesn't show up in the teacherclasses table. (To be honest, I could have this backwards - you might want to do a "left outer" instead - I can never remember which is which so I try it one way and if I don't like the results, I try the other direction to see if that improves things).

Hope that helps a little!