CODEWITHSUNDEEP
sqlsql-server
Spam Hater
Spam Hater

Reputation: 1

SQL Query returns one result, does not return 0 or NULL result

I am trying to get the query to list the Advisers and provide a count of active students for each. I can get it to list the advisers who have 1 student, exclude those with more than 1, but can not get it to return the advisers with 0 or NULL count.

Select Advisors.AdvisorID, Advisors.FirstName, Advisors.LastName, COUNT(case Students.IsActive WHEN '1' then 1 else NULL end) AS "Number of Students"
FROM Advisors, Students
WHERE Advisors.AdvisorID=Students.AdvisorID
GROUP BY Advisors.AdvisorID, Advisors.FirstName, Advisors.LastName
HAVING COUNT(case Students.IsActive WHEN '1' then 1 else NULL end)='1'

counts the active studnets, and returns the advisor list with the advisor with one student, the advisers with 0 students come back blank. What am I missing?

Select Advisors.AdvisorID, Advisors.FirstName, Advisors.LastName, COUNT(case Students.IsActive WHEN '1' then 1 else NULL end) AS "Number of Students"
FROM Advisors, Students
WHERE Advisors.AdvisorID=Students.AdvisorID
GROUP BY Advisors.AdvisorID, Advisors.FirstName, Advisors.LastName
HAVING COUNT(case Students.IsActive WHEN '1' then 1 else NULL end) IS NULL

comes back with the column names and no data. I have double checked the tables advisor table has 3 entries, one has 2 active students and one inactive 0 or 1 using bit, one has no students, and one has one.

Using <= 1 or < 1 similarly result in blank data.

Upvotes: 0

Views: 80

Answers (1)

Squirrel
Squirrel

Reputation: 24763

please use ANSI JOIN syntax

Select    Advisors.AdvisorID, 
          Advisors.FirstName, 
          Advisors.LastName, 
          COUNT(case Students.IsActive WHEN '1' then 1 else NULL end) AS "Number of Active Students"
FROM      Advisors
LEFT JOIN Students
ON        Advisors.AdvisorID=Students.AdvisorID
GROUP BY  Advisors.AdvisorID, 
          Advisors.FirstName, 
          Advisors.LastName
HAVING COUNT (Students.AdvisorID) = 1

Upvotes: 1

Related Questions