CODEWITHSUNDEEP
sql
Otoniel
Otoniel

Reputation: 17

Where to use WHERE clause in this query?

I want only results WHERE COUNT = 0, but every way I tried making this query it failed. Please help!

SELECT 
    username, 
    COUNT(user_id) AS COUNT 
FROM 
    users 
LEFT JOIN 
    photos ON users.id = photos.user_id 
GROUP BY 
    username 
ORDER BY 
    COUNT ASC;

Upvotes: 0

Views: 39

Answers (2)

The Impaler
The Impaler

Reputation: 48865

Use HAVING. For example:

SELECT username, COUNT(user_id) AS COUNT 
FROM users 
LEFT JOIN photos ON users.id = photos.user_id 
GROUP BY username 
HAVING COUNT = 0
ORDER BY COUNT ASC;

HAVING filters "resulting rows" after they are aggregated. WHERE, on the other side, filters rows before they are aggregated.

Or... you can use an anti-join:

select u.username from users u
left join photos p on u.id = p.user_id 
where p.user_id is null

Upvotes: 2

Stu
Stu

Reputation: 32614

Since you're only interested in users without and photos you don't actually need to count anything:

select username
from users u
where not exists (select * from photos p where p.user_id = u.id);

Upvotes: 2

Related Questions