CODEWITHSUNDEEP
mysqlsqljoin
gentrobot
gentrobot

Reputation: 671

Find unique id from two tables and corresponding counts from both tables

I am running a mysql database. I have two tables that store the likes and comments on posts respectively.

Likes table:

+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| post_id      | int(11)          | NO   |     | NULL    |                |
| user_id      | varchar(255)     | NO   |     | NULL    |                |
| created_at   | timestamp        | YES  |     | NULL    |                |
| updated_at   | timestamp        | YES  |     | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+

Comments Table:

+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| post_id      | int(11)          | NO   |     | NULL    |                |
| user_id      | varchar(255)     | NO   |     | NULL    |                |
| comment      | varchar(255)     | NO   |     | NULL    |                |
| created_at   | timestamp        | YES  |     | NULL    |                |
| updated_at   | timestamp        | YES  |     | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+

I need to get unique user ids and their likes and comment counts.

I ran the following query:

select * from
(select user_id, count(*) as like_count from post_likes group by user_id) as a,
(select user_id, count(*) as comment_count from post_comments group by user_id) as b
limit 5

The query took 66 seconds on 1.8 million records (both tables combined). Also, it didn't give correct result.

If I do a LEFT JOIN, it returns only matching records from the left table, whereas I need from both, i.e. if a user id has only liked and not commented or only commented and not liked.

What I am trying to get is something like:

+--------------+------------------+------+-----+---------+
| user_id      | like_count       | comment_count        |
+--------------+------------------+------+-----+---------+
| 1            | 1213             | 310                  |
| 2            | 1098             | 0                    |
| 3            | 0                | 115                  |
+--------------+------------------+------+-----+---------+

Upvotes: 0

Views: 51

Answers (3)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

One way is to use UNION ALL and conditional aggregation:

SELECT user_id,
       SUM(type='likes') AS like_count,
       SUM(type='comments') AS comment_count
FROM (
   SELECT user_id, 'likes' AS type
   FROM post_likes 

   UNION ALL

   SELECT user_id,  'comments' AS type
   FROM post_comments) AS t
GROUP BY user_id

This query will not return users having no likes and no comments.

Upvotes: 0

Darshan Mehta
Darshan Mehta

Reputation: 30819

An ideal way would be to LEFT JOIN on user table, e.g.:

SELECT u.user_id, COUNT(l.id), COUNT(c.id)
FROM users u LEFT JOIN Likes l ON u.user_id = l.user_id
LEFT JOIN Comments c ON u.user_id = c.user_id;

Upvotes: 3

jarlh
jarlh

Reputation: 44766

Do a UNION ALL instead, and then merge the rows:

select user_id, sum(like_count), sum(comment_count)
from
(
    select user_id, count(*) as like_count, null as comment_count
    from post_likes
    group by user_id
    union all
    select user_id, null, count(*)
    from post_comments
    group by user_id
) as dt
group by user_id

Upvotes: 2

Related Questions