etm124 etm124 - 1 month ago 5
SQL Question

Zero if no value returned

I have the following query:

SELECT
f.name,
COUNT(distinct(p.id))
FROM
problems p INNER JOIN problem_list pl on p.problem_list_id = pl.id
FULL OUTER JOIN facilities f on f.id = p.facility
WHERE
p.problem_list_id = '100'
AND f.name in ('CRJ','TVRJ','WRJ')
GROUP BY
f.name
ORDER BY
f.name


When this query is run, sometimes one of the
facilities
does not return a result. In that case, I'd still like my result set to show that facility, but return a 0.

For example:

CRJ | 0|
TVRJ | 12|
WRJ | 2|


I've tried to use
coalesce
, and adjusting my joins on the
facility
table, but it doesn't seem to be working.

Answer

I don't think that a FULL JOIN is needed, just a LEFT JOIN:

SELECT  
    f.name, 
    ISNULL(COUNT(DISTINCT p.id),0) N
FROM 
    facilities f 
    LEFT JOIN ( SELECT * 
                FROM problems
                WHERE problem_list_id = '100') p 
        ON f.id = p.facility
    LEFT JOIN problem_list pl 
        ON p.problem_list_id = pl.id 
WHERE  
    f.name in ('CRJ','TVRJ','WRJ')
GROUP BY
    f.name
ORDER BY
    f.name;