Tutorial :MySQL JOIN with 3 tables and COUNT() not working



Question:

I'm having a problem with my MySQL statement. I need a query that counts the number of comments and the number of topics a user has created. My table structure is something like this:

Table 'users'  -------------  user_id  user_name  ...    Table 'topics'  --------------  topic_id  topic_user_id  ...    Table 'topiccomments'  ---------------------  topiccomment_id  topiccomment_user_id  ...  

So far I've been able to produce this query:

SELECT       u.user_id,       u.user_name,      COUNT(t.topic_user_id) as topic_count,      COUNT(tc.topiccomment_user_id) as topiccomment_count  FROM       users as u      JOIN topiccomments as tc ON u.user_id = tc.topiccomment_user_id      JOIN topics as t ON u.user_id = t.topic_user_id  WHERE       u.user_id = t.topic_user_id AND      u.user_id = tc.topiccomment_user_id   GROUP BY       u.user_id  

This query is executed, but the 'topic_count' and 'topiccomment_count' values are totally wrong and I don't quite understand why.

I was hoping somebody here could help me out?


Solution:1

change to

COUNT(DISTINCT t.topic_id) as topic_count,  COUNT(DISTINCT tc.topiccomment_id) as topiccomment_count  

This will count the number of distinct topics and topic comments that match the user ID. Before, you were counting the number of rows in the cross-product of topics and topic comments for a given user.

If it works in your situation, I would refactor this into two queries, one for counting topics and one for topic_comments, since this will be more efficient.


Solution:2

quick shot: try replacing count(field) with count(distinct field)


Solution:3

First of all, you can delete your entire WHERE clause. It is not necessary because you already took care of it in the JOINs.

To fix your issue, use this in your SELECT clause instead of the current COUNT statements your have:

COUNT(DISTINCT t.topic_id) as topic_count,  COUNT(DISTINCT tc.topiccomment_id) as topiccomment_count  

You are trying to count the number of topics, or topic comments. Not the number of users (which should always be 1).


Solution:4

The JOINs are probably returning a cartesian product of the topiccomments and topics tables because there is no restriction between their relationship, which could explain why you are getting a high count.

One easy way to tackle this problem is to use correlated subqueries:

SELECT  u.user_id,           u.user_name,          SELECT (COUNT(*) FROM topics t WHERE t.id = u.id),          SELECT (COUNT(*) FROM topiccomments tc WHERE tc.id = u.id)  FROM    users u;  

You can also use COUNT(DISTINCT t.topic_id) and COUNT(DISTINCT tc.topiccomment_id) in your original query as some of the other answers suggest. In fact, that may turn out to be more efficient in terms of performance.


Solution:5

You should be counting the topic and comment ids, not the user_ids of the comment/topic.

SELECT       u.user_id,       u.user_name,      COUNT(DISTINCT t.topic_id) as topic_count,      COUNT(DISTINCT tc.topiccomment_id) as topiccomment_count  FROM       users as u      JOIN topiccomments as tc ON u.user_id = tc.topiccomment_user_id      JOIN topics as t ON u.user_id = t.topic_user_id  GROUP BY       u.user_id  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »