SQL查询返回一个结果，不返回0或NULL结果

Question

我正在尝试让查询列出Advisers，并为每个Advisers提供活跃学生的计数。我可以让它列出有1名学生的顾问，排除那些有超过1名学生的顾问，但不能让它返回0或空计数的顾问。

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'

对活动学生网络进行计数，并返回具有1名学生的指导教师列表，0名学生的指导教师返回空白。我遗漏了什么？

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 

返回列名，但没有数据。我已经仔细检查了表的辅导表有3个条目，一个有2个活跃的学生和一个不活跃的0或1使用位，一个没有学生，一个有一个。

使用<= 1或<1同样会产生空白数据。

Answer 1

请使用ANSI连接语法

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