MySQL JOIN with 3 tables and COUNT() not working

Question

Hi,

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?

Thanks!

Answer 1

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

Answer 2

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).

Answer 3

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.

Answer 4

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

Answer 5

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.