SELECT DISTINCT a.StudentID, a.SFirstName
FROM shankar_detail_profile a, shankar_courses c
LEFT JOIN shankar_assignment b ON a.StudentID = b.StudentID
WHERE b.StudentID IS NULL AND c.CoordinatorID =100200300
works well.. I think..
The reason b.AssignID = 1 or whatever doesn't work, is the joins that fail, all b fields are set to NULL.
[SOLVED] left join help
Moderator: General Moderators
-
psmshankar
- Forum Commoner
- Posts: 96
- Joined: Tue Aug 06, 2002 4:25 am
- Location: India
-
psmshankar
- Forum Commoner
- Posts: 96
- Joined: Tue Aug 06, 2002 4:25 am
- Location: India
If you have to select distincct, there is a good chance you are doing something wrong. What you really mean is:
SELECT A.StudentID, A.SFirstName
FROM Student_Detail_Profile AS A, Student_Courses AS B
LEFT OUTER JOIN Stud_Assignment AS C on (B.StudentID=C.StudentID AND C.AssignID=89)
WHERE C.StudentID IS NULL and A.StudentID=B.StudentID and B.CoordinatorID=1082370452
If you have any conditions in your where clause referencing the right table in a left join and they aren't allowing for NULL, your left join will function exactly the same as a normal join. The C.AssignID=89 was to blame.
What feyd suggested won't work if no student has turned it in yet. However if you want to do it that way you can have a table containing all of the assignments, not storing anything about who turned it in, and use that in place of table d in feyd's query. It would also make a good place to store things like date assigned, due date, max number of points and such.
SELECT A.StudentID, A.SFirstName
FROM Student_Detail_Profile AS A, Student_Courses AS B
LEFT OUTER JOIN Stud_Assignment AS C on (B.StudentID=C.StudentID AND C.AssignID=89)
WHERE C.StudentID IS NULL and A.StudentID=B.StudentID and B.CoordinatorID=1082370452
If you have any conditions in your where clause referencing the right table in a left join and they aren't allowing for NULL, your left join will function exactly the same as a normal join. The C.AssignID=89 was to blame.
What feyd suggested won't work if no student has turned it in yet. However if you want to do it that way you can have a table containing all of the assignments, not storing anything about who turned it in, and use that in place of table d in feyd's query. It would also make a good place to store things like date assigned, due date, max number of points and such.