CODEWITHSUNDEEP
mysqlsql
Dziad Borowy
Dziad Borowy

Reputation: 12579

Frequency join statement

I have 2 tables:

users:

id | name
-----------
 1 | user 1
 2 | user 2
 3 | user 3

and posts:

id | userId | text
--------------------
 1 |      1 | text 1
 2 |      1 | text 2
 3 |      2 | text 3
 4 |      2 | text 4
 5 |      2 | text 5
 6 |      2 | text 6

I need to retrieve users ordered by post-frequency, e.g.:

id | name   | posts
-------------------
 2 | user 2 |     4
 1 | user 1 |     1
 3 | user 3 |     0

Some users might not have posts!

Currently I have 2 queries and doing it in 3 steps:

  1. retrieve all users
  2. retrieve all posts grouped by userId
  3. use php to join the above

Question

Is the above possible to do in a single sql query?

Upvotes: 0

Views: 69

Answers (2)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Another version, which prevents listing all fields from users table in group by clause. Also more fast in many cases IMO.

select u.id, u.name, coalesce(c.cnt, 0) as posts
from users u
left join 
  (select userId, couint(*) cnt from posts group by userId) c
on u.id = c.userId

Upvotes: 1

juergen d
juergen d

Reputation: 204756

select u.id, u.name, count(p.id) as posts
from users u
left join posts p on p.userid = u.id
group by u.id, u.name
order by posts desc

Upvotes: 3

Related Questions