Hi, I have a database which keeps track of STUDENTS doing CLASSES. Each CLASS has a list of ASSESSMENTs and the database stores a GRADE for each each STUDENT in the CLASS.
The table structures are as follows: class: CID(*), Title, CourseCode, Year student: SID(*), FirstName, SecondName, StudentNo, Year, CID(FK) assessment: AID(*), AssName, Weight, DueDate, CID(FK) grade: SID(*)(FK), AID(*)(FK), Grade, Shown Is it possible to create an SQL query to do the following: I want to list every STUDENT alongside every ASSESSMENT listed as being in the CLASS that STUDENT is in, along with the GRADE the student received in that ASSESSMENT. The query I was using was as follows: SELECT FirstName, SecondName, AssName, Grade FROM student, assessment, grade WHERE grade.SID = student.SID AND grade.AID = assessment.AID AND student.CID = 1 (say). This lists all the GRADEs for all the STUDENTs in CLASS 1. My problem is that I want to do this with a LEFT JOIN so that even if the STUDENT did not take a particular ASSESSMENT (and so there is no entry with that combination of SID and AID in the GRADE table), the entry will still be returned, just with a NULL value. I was trying this with various combinations of LEFT JOINs on the three tables, but none of them seemed to work. Thank you. ------------------------ Michael Cooney [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
