Search code examples
mysqlsqljoincorrelated-subquery

MySQL - how to combine three tables to get the counts

There are three tables, I would like to get the count of a user's total tweets and the count of likes his total tweets received.

I tried to combine two queries to get what I want but failed. Have looked through several previous questions but still can't figure it out.

  • Users table
id name
1 User1
  • Tweets table
id UserId (foreign key) content
1 User1 hello
  • Likes table
id UserId (foreign key) TweetId (foreign key)
1 User1 hello

First query:

SELECT Users.name, Users.id, COUNT(Tweets.UserId) AS UserTweetCount FROM Users
LEFT JOIN Tweets
ON Users.id = Tweets.UserId
GROUP BY Users.id
ORDER BY UserTweetCount DESC;

enter image description here

Second query:

SELECT Users.name, Users.id, COUNT(Likes.UserId) AS UserTweetBeLikedCount FROM Users
LEFT JOIN Likes
ON Users.id = Likes.UserId
GROUP BY Users.id;

enter image description here

I tried like below but would get wrong UserTweetBeLikedCount counts. The counts would be UserTweetCount's, not UserTweetBeLikedCount's. When I ran two queries separately, it worked well. But when I combined them together, it didn't work right. Don't know how to display the right counts. Can someone give me hints to solve this, please?

SELECT Users.name, Users.id, 
COUNT(Tweets.UserId) AS UserTweetCount, COUNT(Likes.UserId) AS UserTweetBeLikedCount 
FROM Users
LEFT JOIN Tweets
ON Users.id = Tweets.UserId
LEFT JOIN Likes
ON Users.id = Likes.UserId
GROUP BY Users.id
ORDER BY UserTweetCount DESC;

enter image description here

Solution

  • I recommend using correlated subqueries for this:

    SELECT u.*,
       (SELECT COUNT(*)
        FROM Tweets t
        WHERE u.id = t.UserId
       ) AS UserTweetCount,
       (SELECT COUNT(*)
        FROM Likes l
        WHERE u.id = l.UserId
       ) AS UserLikeCount
FROM Users u
ORDER BY UserTweetCount DESC;

    As a note: For performance, you want indexes on Tweets(UserId) and Likes(UserId).