[SOLVED] left join help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
psmshankar
Forum Commoner
Posts: 96
Joined: Tue Aug 06, 2002 4:25 am
Location: India

Post 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..??
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
psmshankar
Forum Commoner
Posts: 96
Joined: Tue Aug 06, 2002 4:25 am
Location: India

Post by psmshankar »

that is fantastic...
thanks a lot feyd...
:D
tbp105
Forum Newbie
Posts: 2
Joined: Wed Jul 07, 2004 11:16 pm

Post 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.
Post Reply