CODEWITHSUNDEEP
mysqlsql
rlcabral
rlcabral

Reputation: 1546

Select to return NULL on left join

I have two tables with the following data:

Users

user    | online
-------- -------
1       | 1
2       | 0
3       | 1
4       | 1
5       | 0

Photos

user_id | photo
-------- -------
1       | photo1.jpg
1       | photo2.jpg
2       | photo3.jpg
3       | photo4.jpg

I want to select all users that are online (online = 1) whether the user has a photo or not, showing the count or null, or zero, if the user has no photos. Using the tables above, it would be something like this:

user_id | count
-------- ------
1       | 2
3       | 1
4       | null

Upvotes: 1

Views: 28

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

Try this:

select
    u.user user_id,
    case when count(p.user_id) > 0 then count(p.user_id) end `count`
from users u
left join photos p on u.user = p.user_id
where u.online = 1
group by u.user;

You can Left join the photos to users and group it over the user to find count. If count is more than 0 then get the count or else null.

Upvotes: 1

Related Questions