Joining multiple tables

I need your help on this. I am not getting the results i wanted in this script.

select distinct s.firstname, s.lastname, c[coursename],(midterm+final+assignment) as "total mark"

from student as s join studentrecord as sr

on (s.student#=sr.student#)

join course as c on (sr.course#=c.course#)

join courseenrollment as ce on (s.student#=ce.student#)

where ce.[status]=1

Now the results should only pick up 10 students who are enrolled (status=1) but the program is bypassing the where clause and returns all the results even though students were enrolled or not. Let me kknow your thoughts.

Join logic is applied in order, so if you rewrite your query like this it should provide the results expected.

1st, join the student and enrollment table on the student# using only the records with a status = 1 - this will return only students

2nd, join the student record table by the student# - this joins to the resultset of the 1st join

3rd, join the course table to the resultset of the previous joins by course# to the studentrecord table AND to the enrollment table.

Code Snippet

SELECT DISTINCT s.firstname, s.lastname, c.coursename,(midterm+final+assignment) as [total mark]

FROM student s INNER JOIN courseenrollment ce on ce.[student#] = s.[student#] AND ce.status = 1

INNER JOIN studentrecord sr on s.[student#] = sr.[student#]

INNER JOIN course c on c.[course#] = sr.[course#] AND c.[course#] = ce.course#]

Try this, and repost if it doesn't work or the assumption I made about the courseenrollment table having a course# field is incorrect.

|||

This is unexpected but logically true. If you add ce.status to the SELECT portion, I'm sure you'll see that the status = 1 for all records

Adamus