当count(*) = 0时，使用WHERE子句时，SQL查询不返回行

I'm trying to write a query that returns the name of the school, the year of the payment, the cost of the payment and the number of students there was the year of the payment. The problem I encounter is that for a certain year there is 0 student so the query doesn't return any row although there was a payment.

我在写一个查询返回学校的名字，支付的年份，支付的成本以及学生的数量。我遇到的问题是，在某一年有0个学生，所以查询不会返回任何行，尽管有支付。

Here is what i tried :

以下是我的尝试:

SELECT School.NAME,
       Payment.year,
       Payment.amount,
       Count(Student.id_stud) AS 'nb stud'
FROM   School
       LEFT JOIN Student
              ON school.id_school = Student.id_school
       LEFT JOIN Payment
              ON School.id_school = Payment.id_School
WHERE  Year(Student.date_in) <= Payment.year
GROUP  BY School.NAME,
          Payment.amount,
          Payment.year 

I want to display every row even if the COUNT(Student.id_stud) is 0. I think that the problem is with the WHERE clause .

我想显示每一行，即使计数(Student.id_stud)为0。我认为问题出在WHERE子句上。

2 个解决方案

#1

Move the Year(Student.date_in) filter to ON condition

将Year(Student.date_in)过滤器移动到一个条件下

SELECT School.NAME,
       Payment.year,
       Payment.amount,
       Count(Student.id_stud) AS 'nb stud'
FROM   School
       LEFT JOIN Student
              ON school.id_school = Student.id_school   
       LEFT JOIN Payment
              ON School.id_school = Payment.id_School
             AND Year(Student.date_in) <= Payment.year
GROUP  BY School.NAME,
          Payment.amount,
          Payment.year 

Where you filter the Year(Student.date_in) in Where clause, for the non matching Student.date_in will have NULL values. Those NULL values will be filtered by the where condition. So Move the filter to ON clause it tells what are records to be joined instead of filtering the result. Same logic will be applied to Payment.year as well.

在Where子句中，为不匹配的学生过滤Year(Student.date_in)。date_in将具有空值。这些空值将被where条件过滤。因此，将过滤器移动到ON子句，它将告诉要连接的记录是什么，而不是过滤结果。同样的逻辑也适用于支付。一年。

#2

There could be two reasons why there are not any result 1. The value of the field which is used in the comparison is null 2. There is left join so there is a chance that there may not be the matching record for Student or Payment so if those are used in where condition then there must be matching record. you can try below query which is checking for null value as well.

有两个原因可以解释为什么没有结果1。在比较中使用的字段的值为null 2。有留下的连接，所以有可能没有学生或支付的匹配记录，所以如果这些在条件中使用，那么必须有匹配记录。您可以尝试下面的查询，查询也在检查空值。

SELECT School.NAME,
       Payment.year,
       Payment.amount,
       Count(Student.id_stud) AS 'nb stud'
FROM   School
       LEFT JOIN Student
              ON school.id_school = Student.id_school
       LEFT JOIN Payment
              ON School.id_school = Payment.id_School
WHERE  Year(Student.date_in) <= Payment.year
OR Student.date_in is null OR Payment.year is null
GROUP  BY School.NAME,
          Payment.amount,
          Payment.year