Getting data from multiple related tables mysql

Question

I have some related tables

USERS

+----+--------------------------+------------+
| id | email                    | first_name |
+----+--------------------------+------------+
|  2 | [email protected] | Loren      |
|  3 | [email protected]    | Fernando   |
|  4 | [email protected]          | Evelyn     |
+----+--------------------------+------------+

EVENTS

+----+------------+
| id | name       |
+----+------------+
|  1 | Dolores ve |
|  2 | Harum erro |
|  3 | Ratione qu |
+----+------------+

EVENT_USER (join)

+---------+----------+
| user_id | event_id |
+---------+----------+
|       1 |        3 |
|       1 |       13 |
|       1 |       48 |
+---------+----------+

along with other tables related to user ( messages, connections ) etc

I want to get a count of messages, connections and other related data for users of a specific event

SQL I've tried:

SELECT u.`id`, u.`first_name`, u.`last_name`, 
COUNT(m.`id`) as message_count,
COUNT(a.`id`) as article_count
FROM `users` u
LEFT JOIN `event_user` eu ON u.`id` = eu.`user_id`
LEFT JOIN `messages` m ON u.`id` = m.`from`
LEFT JOIN `articles` a ON u.`id` = a.`user_id`
WHERE
eu.`event_id` = 3
GROUP BY u.`id`

But the counts are coming out to be same for both columns.

i.e. message_count and article_count are same for all users.

Could you please create a fiddle sqlfiddle.com with some sample data from all the tables ? — Abhik Chakraborty
– Abhik Chakraborty, Commented Oct 8, 2015 at 14:20

Gordon Linoff · Accepted Answer · 2015-10-08 14:22:51Z

1

The easiest way to solve this is using count(distinct):

SELECT u.`id`, u.`first_name`, u.`last_name`, 
       COUNT(DISTINCT m.`id`) as message_count,
       COUNT(DISTINCT a.`id`) as article_count

If the values are large, then you might want to rewrite the query to aggregate before doing the joins.

The reason that you are getting the same values is that count(<column>) simply counts the number of non-null values. So, your query is saying that all users have both messages and articles (if one or the other were missing, the left joins would produce NULL values).

answered Oct 8, 2015 at 14:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Angad Dubey Over a year ago

This works. Im trying to wrap my head around the concept though.

Collectives™ on Stack Overflow

Getting data from multiple related tables mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related