SQL query using information from 4 tables (not all directly linked)
- by Yvonne
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 :)
