MySQL查询多个LEFT加入问题

Question

I am trying to get the required result from the following query but it doesnt seem to work...

SELECT DISTINCT
u.user_name as user_name,
u.total_points as total_points,
u.user_id as user_id,
COUNT(a.id) as user_total_articles_published,
COUNT(r.id) as user_total_replies_published,
COUNT(v.id) as user_total_votes_done
FROM users as u
LEFT JOIN articles as a ON u.user_id=a.user_id
LEFT JOIN replies as r ON u.user_id=r.user_id
LEFT JOIN votes as v ON u.user_id=v.user_id
GROUP BY u.user_id
ORDER BY u.total_points DESC
LIMIT 10

If i remove the last 2 LEFT jOINS the query will work... whats wrong with the other 2? Do i have to use another method for this to work?

thanks

Answer 1

I think by 'not working' you mean that the query returns too many records? That is because of the combination of joins. You return each reply for each article record, so the numbers are multiplied. You can solve this by using DISTINCT in the COUNT. That way, you count the unique id's, so you count each article only once:

COUNT(distinct a.id) as user_total_articles_published,
COUNT(distinct r.id) as user_total_replies_published,
COUNT(distinct v.id) as user_total_votes_done

[edit]

A possibly faster solution, eliminating the need for DISTINCT and GROUP BY:

SELECT
  u.user_name as user_name,
  u.total_points as total_points,
  u.user_id as user_id,
  (SELECT COUNT(a.id) FROM articles a 
   WHERE a.user_id = u.user_id) as user_total_articles_published,
  (SELECT COUNT(r.id) FROM replies r 
   WHERE r.user_id = u.user_id) as user_total_replies_published,
  (SELECT COUNT(v.id) FROM votes v 
   WHERE v.user_id = u.user_id) as user_total_votes_done
FROM users as u
ORDER BY u.total_points DESC
LIMIT 10