homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Query for 4 tables, without 1 direct join (possibly outer join?)
Display all teachers subjects in single place

Msg#: 4112328 posted 1:36 pm on Apr 8, 2010 (gmt 0)

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 :)



10+ Year Member

Msg#: 4112328 posted 1:59 pm on Apr 9, 2010 (gmt 0)

INNER JOIN only returns rows that match. If you want to force all subjects to return, you would need to use a LEFT OUTER JOIN.

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved