MySQL return rows when count(*)=0

Question

I have two tables:

Table users:

userId, user
------------
0, Alice
1, Bob
2, Eve

Table rss:

userId, link, read
------------
0, example.com/1, 0
0, example.com/2, 1
0, example.com/3, 1
0, example.com/4, 0
1, example.com/3, 0
1, example.com/4, 1

When I run:

SELECT `users`.`user`, COUNT(*) FROM `rss` INNER JOIN `users` ON `rss`.`userId`=`users`.`userId` WHERE `rss`.`read`=0 GROUP BY `rss`.`userId`

I get:

Alice, 2
Bob, 1

But I would like to also have Eve mentioned (stating 0), i.e.,

Alice, 2
Bob, 1
Eve, 0

Gordon Linoff · Accepted Answer · 2014-06-16 10:30:04Z

3

You need an outer join:

SELECT u.`user`, COUNT(rss.userId)
FROM users u LEFT JOIN
     rss
     ON rss.userId = u.userId AND rss.`read` = 0
GROUP BY u.userId;

And, I made the following changes:

Add alias for users so the query is easier to read and write.
Switched the tables so I could use left join instead of right join. I think left join is easier to read.
Changed the group by to use the field from the users table.
Moved the condition on rss to the on clause.
Removed most of the back ticks. They just clutter the query.

answered Jun 16, 2014 at 10:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL return rows when count(*)=0

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related