Page 2 of 2
Posted: Mon Jun 14, 2004 3:04 am
by feyd
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.
Posted: Mon Jun 14, 2004 3:30 am
by psmshankar
yep, without AssignID, it works. But that is NOT the correct result...
Because I need to get the students who have NOT DONE A PARTICULAR ASSIGNMENT..
So obviously I shud have that AssignID criteria somewhere.
Can tell me any other way to go about it..??
Posted: Mon Jun 14, 2004 3:52 am
by feyd
SELECT DISTINCT a.StudentID, a.SFirstName
FROM shankar_detail_profile a, shankar_courses c, shankar_assignment d
LEFT JOIN shankar_assignment b ON a.StudentID = b.StudentID
WHERE b.StudentID IS NULL AND c.CoordinatorID = 100200300 AND d.AssignID = 1
Posted: Mon Jun 14, 2004 4:07 am
by psmshankar
that is fantastic...
thanks a lot feyd...

Posted: Wed Jul 07, 2004 11:16 pm
by tbp105
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.