CODEWITHSUNDEEP
sqlsql-server
etm124
etm124

Reputation: 2140

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.

Upvotes: 0

Views: 150

Answers (2)

Derrick Moeller
Derrick Moeller

Reputation: 4970

Your original query started with problems, if you want to include facilities that haven't had problems you likely want to start with facilities.

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

Upvotes: 1

Lamak
Lamak

Reputation: 70678

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;

Upvotes: 3

Related Questions