SQL Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Help

Author

Topic

skylar
Starting Member

2 Posts

Posted - 2003-07-29 : 13:03:16

Can anyone help me out with a little bit of SQL? Below is my SQL statment so far...

SELECT * FROM Students
LEFT JOIN Subscription ON
(Students.StudentsID = Subscription.StudentsID OR
Subscription.StudentsID IS null)
WHERE
Subscription.CourseID = 8 OR
Subscription.CourseID IS null
ORDER BY Students.LastName

Basically what I have here is 2 tables. One that stores students and one that stores what courses they are subscribed to. It works nicely, except if a student is subscribed to more then 1 Course (and that courseID is not 8), then they wont be returned in the results. I just basically want to have every student listed and if there is no subscription for CourseID 8 (for example) to have the subscription as NULL. Any help on this would GREATLY be appreciated!

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-07-29 : 13:54:12

If I understand you correctly this should work

SELECT *
FROM Students LEFT JOIN Subscription
ON Students.StudentsID = Subscription.StudentsID
--- OR Subscription.StudentsID IS null shouldn't need this here
AND Subscription.CourseID = 8
--- WHERE Subscription.CourseID IS null
ORDER BY Students.LastName

It will give you everything from students table and matching data in subscription table where join condition exists and subscription id = 8.

skylar
Starting Member

2 Posts

Posted - 2003-07-29 : 14:29:52

Right, but that only will return the students that ARE subscribed to the courseID 8. I want to return everybody reguardless if they are subscribed or not

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-07-29 : 14:39:55

I apologize if this was unclear but,

quote:

It will give you everything from students table and matching data in subscription table where join condition exists and subscription id = 8.

tried to explain that ALL student information is returned regardless of enrollment/subscription (accomplished with LEFT JOIN)

AND

Subscription information is only returned when the studentid exists in the subscription table where "subscriptionid = 8" (accomplished by putting condition 'Subscription.CourseID = 8' in the FROM clause instead of WHERE clause)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources