SELECT from one table where count of rows = 0 in another table

Question

SELECT from one table where count of rows = 0 in another table

jmorello

Grasshopper

Points: 24
More actions
September 27, 2010 at 7:07 pm

#241018

So I two tables that I am working with here: "Students" and "Schedule".
What I am trying to do is select every student that has the Students.SchoolStatus column set to 13 or 14, and of those results, select the ones that have no rows in the Schedule table with the TermID column set to 352
In English, what I'm trying to do is find students that are marked "active" (13 or 14 in students.schoolstatus) that are registered for zero classes in a given term (schedule.termid 352 in this case). The primary key for the Students table (Students.StudentID) correlates to a column in the Schedule table (Schedule.StudentID) with a one-to-many relationship. Each row in the Schedule table is one student registered for one class in one term.
This was pretty hard to explain in English so I understand if it is confusing. I'm not sure if I should be using a subquery or what... I keep getting stuck thinking "How do I select what ISN'T there?" Any help would be appreciated greatly and let me know if anyone would like any clarification.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

UMG Developer SSChampion Points: 13482 More actions · Answer 1

Actually that isn't a bad description of your problem, though it sounds a little bit like homework. Here is an article that can help you submit better questions that would allow people to more easily test their solutions: http://www.sqlservercentral.com/articles/Best+Practices/61537/

There are a number of ways to accomplish that, for example you could use LEFT JOIN or NOT EXISTS. I prefer the NOT EXISTS because it makes it more clear what you are doing, and has no chance of creating a cartesian.

But in any case give this a try:

SELECT

Students.*

FROM dbo.Students Students

WHERE

Students.SchoolStatus IN (13, 14)

AND NOT EXISTS (SELECT NULL FROM dbo.Schedule Schedule WHERE Schedule.StudentID = Students.StudentID AND Schedule.TermID = 352);

By not having the table definitions I didn't know if the fields are number or character fields, so you might need to make some adjustments in the WHERE clause(s).

jmorello Grasshopper Points: 24 More actions · Answer 2

That worked perfectly! Thanks so much. It does sound a bit like homework... I work for a school and Crystal Reports recently became one of my job duties. 😛

UMG Developer SSChampion Points: 13482 More actions · Answer 3

Your welcome, and thanks for letting me know it worked for you.