SQL statement to include rows that have no matches in a correlated subquery

Question

I have the correlated subquery below. Each id (147,148,149) in table1 has many records in table2. The id 148 however has no records that match the time condition in the statement. Therefore it is not included in the output. I want it to be included with a 0 for the count column. What needs to be changed?

SELECT b.fkid, COUNT(DISTINCT username)
FROM table2 AS b
WHERE
b.fkid IN ( 147,148,149 )
AND time > (SELECT SUBTIME(a.endTime, SEC_TO_TIME( 60*60 )) FROM table1 AS a WHERE a.id = b.fkid)
GROUP BY b.fkid

This statement returns:

b.fkid   COUNT(DISTINCT username)
147      41
149      26

I want it to return:

b.fkid   COUNT(DISTINCT username)
147      41
148       0
149      26

Okay I got the solution. It is a modified version of rexem's answer:

SELECT t.fkid,
   IFNULL(nu.num_users, 0)
FROM TABLE_2 t
LEFT JOIN (SELECT t.fkid,
                  COUNT(DISTINCT t.username) 'num_users'
           FROM TABLE_2 t
           JOIN TABLE_1 a ON a.id = t.fkid
                          AND SUBTIME(a.endTime, SEC_TO_TIME( 60*60 )) < t.time
           GROUP BY t.fkid) nu ON nu.fkid = t.fkid
WHERE t.fkid IN (147, 148, 149)
GROUP BY t.fkid, nu.num_users

Changes from rexem's answer:
"SEC_TO_TIME( 60*60 )) = t.time" to "SEC_TO_TIME( 60*60 )) < t.time"
Removed "t.time" in GROUP BY clause of subquery

Answer 1

Try using COALESCE to handle the scenarios where time is compared to the empty set (e.g., because there are no matches between table1 and table2):

SELECT b.fkid, COUNT(DISTINCT username)
FROM table2 AS b
WHEREb.fkid IN ( 147,148,149 )
AND time > COALESCE((SELECT SUBTIME(a.endTime, SEC_TO_TIME( 60*60 )) FROM table1 AS a WHERE a.id = b.fkid), 0)
GROUP BY b.fkid

COALESCE takes an unbounded set of parameters and, from this group, will return the first non-null parameter.

Answer 2

Try:

   SELECT t.fkid,
          IFNULL(nu.num_users, 0)
     FROM TABLE_2 t
LEFT JOIN (SELECT t.fkid,
                  COUNT(DISTINCT t.username) 'num_users'
             FROM TABLE_2 t
             JOIN TABLE_1 a ON a.id = t.fkid
                           AND SUBTIME(a.endTime, SEC_TO_TIME( 60*60 )) = t.time
         GROUP BY t.fkid) nu ON nu.fkid = t.fkid
   WHERE t.fkid IN (147, 148, 149)
GROUP BY t.fkid, nu.num_users

Answer 3

SELECT a.ID, COUNT(DISTINCT username)
FROM table1 AS a LEFT JOIN table2 AS b
on a.ID = b.fkID
WHERE a.ID IN ( 147,148,149 )
AND b.time > SUBTIME(a.endTime, SEC_TO_TIME( 60*60 )) 
GROUP BY a.ID

Does this help in anyway?

EDIT: I am not sure if this can work in mysql. But, I hope you get the idea to build the query using this.

Answer 4

I think your best chance of getting the zero into the result is using a (disjoint) UNION. The first part would be your current query; the second clause would find the values where there are no relevant entries, selecting the constant 0 for the count column.

Often, an outer join would do the trick. The difficulty here is that the outer join would produce a row of data for ID = 148, and therefore COUNT would give an answer of 1, not 0.