CODEWITHSUNDEEP
mysqljoin
user1066101
user1066101

Reputation: 65

mysql count join double results

Table structure:

activities id, name, tagId

activitiesTags id, title

activitiesReactions id, activityId, message

The query now:

SELECT A.id as activityId, A.name, A.tagId, T.id, T.title, COUNT(R.id)
FROM activities A
LEFT JOIN activitiesTags T
ON A.tagId = T.id
LEFT JOIN activitiesReactions R
ON R.activityId = A.id
GROUP BY A.id
ORDER BY A.id DESC
LIMIT ?

The problem is that I'm getting a reactionCount, but it is far from true, It seems that when an activity has an reaction, it times (*) the amount of actual reactions by two.

Does anyone know where the problem might be? Maybe the ORDER BY after GROUP BY?

Thanks

Upvotes: 0

Views: 667

Answers (2)

Moshe L
Moshe L

Reputation: 1905

@knittl

Some modification to your query:

SELECT a.* , group_concat(T.title)
FROM ( 
    SELECT A.id as activityId, A.name, A.tagId, T.id, T.title, COUNT(R.id) 
    FROM activities A 
    LEFT JOIN activitiesReactions R 
    ON R.activityId = A.id 
    GROUP BY A.id 
    ORDER BY A.id DESC 
    LIMIT ? 
) a 
LEFT JOIN activitiesTags T 
ON a.tagId = T.id

group_concat will display all tags with delimiter (","). your query will display only the first one.

Upvotes: 1

knittl
knittl

Reputation: 265231

You are grouping on activities.id (or activitiesReaction.activityId, whichever way you put it). By joining with your tags table you will get a new row for each tag that matches the given activity. Your count does not double, but is multiplied by the number of tags for each activity.

You want to use a subquery to add the tags to your previous resultset:

SELECT *
FROM (
    SELECT A.id as activityId, A.name, A.tagId, T.id, T.title, COUNT(*)
    FROM activities A
    LEFT JOIN activitiesReactions R
    ON R.activityId = A.id
    GROUP BY A.id
    ORDER BY A.id DESC
    LIMIT ?
) a
LEFT JOIN activitiesTags T
ON a.tagId = T.id

This will give you the correct COUNT, but it will still return multiple rows per activity.

Upvotes: 2

Related Questions