MySQL Left Join With Multiple Counts

Question

I have these 3 tables

users [ id , username, password ]
logs [ id, user_id, action ]
user_propeties [ id, user_id, prop_name, prop_value ]

I want to select All the user's columns with the Count(logs.id) and count(user_propeties.id) Only where prop_value IS NULL from the 2 tables depending on the user id

I have the following query

SELECT t1.*, count(t2.`id`) as total_logs,count(t3.`id`) as total_propeties
                       FROM `users` t1
                       LEFT JOIN `user_propeties` t2
                                ON t1.`id` = t2.`user_id` AND ISNULL(t2.`prop_value`)
                       LEFT JOIN `logs` t3
                                ON t3.`user_id` = t1.`id`                                       
                       GROUP BY t1.id
                       ORDER BY t1.id DESC

Which returns rows but with wrong count value. What i am doing wrong? Thank you

Joachim Isaksson · Accepted Answer · 2014-05-26 18:22:22Z

1

I suspect you want some DISTINCT with that, otherwise it will return the count of all combinations of logs and properties;

SELECT t1.*, COUNT(DISTINCT t2.`id`) as total_logs,
             COUNT(DISTINCT t3.`id`) as total_propeties
FROM `users` t1
LEFT JOIN `user_propeties` t2
         ON t1.`id` = t2.`user_id` AND ISNULL(t2.`prop_value`)
LEFT JOIN `logs` t3
         ON t3.`user_id` = t1.`id`                                       
GROUP BY t1.id
ORDER BY t1.id DESC

answered May 26, 2014 at 18:22

Joachim Isaksson

182k28 gold badges298 silver badges308 bronze badges

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

2 Comments

user3393046 Over a year ago

It worked but i can't get it why. Can you explain me?

Joachim Isaksson Over a year ago

If you have multiple properties and multiple logs, each id may be returned more than once, so a regular COUNT will count all duplicates. COUNT DISTINCT will only count each id once, so if log#4 is returned twice, it will still only be counted once.

Collectives™ on Stack Overflow

MySQL Left Join With Multiple Counts

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related