MySQL查询计数多个表

Question

At the moment, we have 3 queries. In php, we loop over the first, then execute the 2nd multiple times, then which I'd like to have in one single query:

The first query is:

SELECT id FROM users

Then inside looping over those results, the 2nd is

SELECT id AS rid, count(recommendedById) FROM users WHERE id=$id

where $id is users.id from the first query.

The 3rd query is which is executed inside the 2nd loop is:

SELECT count(likes) AS likeCounter FROM posts WHERE author_id=$rid

and likeCounter is summed up to the first query.

Anyone able to bring this into one query?

Desired result The result should be a row per user with a count of users he recommended and a sum of likes his recommended users got on their posts.

Answer 1

SELECT u.id,COUNT(DISTINCT ruid),sum(p.likes)
FROM users as u
LEFT JOIN (SELECT recommendedById as rid,id as ruid from users) as r ON r.rid = u.id
LEFT JOIN posts p ON p.author_id = ruid
GROUP BY u.id