sql server count from different table if not all rows exists

Question

Im trying to get results from 2 tables. The first table there is the Ids with info, from the 2nd table I want to count existing for each id from the 1st table based on some condition. here is my code:

SELECT p.stId, count(att.adate)
FROM payments p
LEFT JOIN Attendence att ON att.stId = p.stId
where p.cyear = 2018 and p.cmonth = 3 and p.cId = 10
and att.adate between '2018/03/01' and '2018/03/30 23:59:59.999' and att.cid 
= 10
GROUP BY p.stId

from the first table(p) I have 3 ids. but when I run this it returns only 1 id. the ids that not exists in the 2nd table (t) it does NOT give any results. what I want to do is that if there is no id in the 2nd table, I want to return a 0 for the count (count(att.adate))

Thanks.

score 1 · Answer 1 · answered May 04 '18 at 07:01

1

Move the right side table conditions from WHERE to ON to get true left join result:

SELECT p.stId, count(att.adate)
FROM payments p
LEFT JOIN Attendence att ON att.stId = p.stId
and att.adate between '2018/03/01' and '2018/03/30 23:59:59.999' and att.cid 
= 10
where p.cyear = 2018 and p.cmonth = 3 and p.cId = 10
GROUP BY p.stId

(When in WHERE, you get regular inner join result.)

answered May 04 '18 at 07:01

jarlh

42,561
8
45
63

Thanks jarlh!! that do the thing!! – RF312 May 04 '18 at 11:40

score 0 · Answer 2 · answered May 04 '18 at 07:06

You are probably missing the results because in your where clause you have conditions for columns in the Attendance table. For cases where there is no match with the payments table these values will be NULL. So I would have your query like this:

SELECT p.stId, count(att.adate)
FROM payments p
LEFT JOIN Attendence att ON att.stId = p.stId
where p.cyear = 2018 and p.cmonth = 3 and p.cId = 10
and (att.adate between '2018/03/01' and '2018/03/30 23:59:59.999' OR att.adate IS NULL) and (att.cid 
= 10 or att.cid IS NULL)
GROUP BY p.stId

score 0 · Answer 3 · edited Jul 29 '20 at 18:42

Your problem is that the where turns the left join into an inner join. I also want to simplify the date arithmetic:

SELECT p.stId, count(a.adate)
FROM payments p LEFT JOIN
     Attendence a
     ON a.stId = p.stId AND a.cid = p.cid AND 
        a.adate >= '2018-03-01' AND 
        a.adate < '2018-04-01
WHERE p.cyear = 2018 and p.cmonth = 3 and p.cId = 10
GROUP BY p.stId;

What the the changes that I've made:

Moved the filtering conditions on the second table to the ON clause. That is how LEFT JOINs work.
I added a JOIN condition on cid, so the comparison to 10 only occurs once (in the WHERE clause).
Changed the date format to YYYY-MM-DD. That is consistent with ISO 8601 standard date formats.
Changed the BETWEEN to >= and < -- I just don't want to think about milliseconds when I'm constructing date ranges at the month level.

A great place to understand why not to use BETWEEN with date/times is Aaron Bertrand's blog What do BETWEEN and the Devil Have In Common.

sql server count from different table if not all rows exists

3 Answers3