SQL- show null, 0 and not present value

user_14569961 0
Viewed 52

I need help.

I have sql code:

SELECT app_users.name,COUNT(owner)
FROM app_users
INNER JOIN app_tickets
ON app_tickets.owner = app_users.id
where app_tickets.status IN (0,1,2,4,5)
GROUP BY app_users.name

This code show value like this:

Adam Smith 12
Brad Smith 23
Nancy Smith 3

But i need to show value like this

Adam Smith 12
Ann Smith 0
Brad Smith 23
Nancy Smith 3
Peter Smith 0

I try use IFNULL() and COALESCE() but not help

subquery left-join count mysql sql
1 Answers

That's a left join:

select u.name, count(t.owner) cnt
from app_users u
left join app_tickets t
    on  t.owner = u.id
    and t.status in (0, 1, 2, 4, 5)
group by u.name, u.id

You could also use a correlated subqueryu:

select u.name,
    (select count(*) from app_tickets t where t.owner = u.id and t.status in (0,1,2,4,5)) cnt
from app_users u

This query would take advantage of an index on app_tickets(owner, status).

user_106767160
Related