Reputation: 612
Yesterday I have been testing one single query for over hours and hours, but I never succeeded. These are the three tables:
USERS:
#### id: 1 ##### name: Admin ##### Hometown: The Hague
POSTS:
#### id: 1 ##### userid: 1 ##### title: Test I ##### opinion: agree
#### id: 2 ##### userid: 1 ##### title: Nope.. ##### opinion: disagree
REACTIONS:
#### id: 1 ##### userid: 1 ##### opinion: agree
#### id: 2 ##### userid: 1 ##### opinion: disagree
And this is what I want: I want the basic information of the user (name, hometown, etc) and I want to count how much compliments (post - opinion: agree), how much complaints (post - opinion: disagree), how much positive reactions (reaction - opinion: agree) and how much negative reactions (reaction - opinion: disagree) this person has posted.
This is the query I use now:
SELECT
u.name, u.hometown,
SUM(IF(r.opinion="disagree",1,0)) AS agrees
SUM(IF(r.opinion="disagree",1,0)) AS disagrees,
SUM(IF(p.opinion="agree",1,0)) AS compliments,
SUM(IF(p.opinion="disagree",1,0)) AS complaints
FROM
users AS u
LEFT JOIN
reactions AS r
ON
r.userid = u.id
LEFT JOIN
posts AS p
ON
p.userid = u.id
WHERE
u.id = 1
The problem is that this does not give me the correct information. It returns values like 8 positive reactions, though there are only two reactions in the DB.
I think it has something to do with GROUP BY p.id, r.id but I tried that and it did not work... Could someone enlighten me?
Thanks in advance!
Upvotes: 1
Views: 287
Reputation: 105
select users.name, users.hometown, myPosts.compliments, myPosts.complaints, myReaction.agrees, myReaction.disagrees
from Users users
LEFT JOIN
(
select post.userid as userid
, SUM(CASE WHEN post.opinion = 'agree' THEN 1 END) as compliments
, SUM(CASE WHEN post.opinion = 'disagree' THEN 1 END) as complaints
from Posts post
group by post.userid
) as myPosts
on users.id = myPosts.userid
LEFT JOIN
(
select reaction.userid as userid
, SUM(CASE WHEN reaction.opinion = 'agree' THEN 1 END) as agrees
, SUM(CASE WHEN reaction.opinion = 'disagree' THEN 1 END) as disagrees
from Reaction reaction
group by reaction.userid
) as myReaction
on users.id = myReaction.userid
Upvotes: 0
Reputation: 43494
What about?
select id, name, hometown, sum(agrees) agrees, sum(disagrees) disagrees,
sum(compliments) compliments, sum(complaints) complaints
from (
select u.id, u.name, u.hometown,
if(p.opinion = 'agree', 1, 0) agrees,
if(p.opinion = 'disagree', 1, 0) disagrees,
0 compliments, 0 complaints
from users u
left join posts p on u.id = p.userid
union all
select u.id, u.name, u.hometown,
0, 0,
if(r.opinion = 'agree', 1, 0),
if(r.opinion = 'disagree', 1, 0)
from users u
left join reactions r on u.id = r.userid
) as S
group by id, name, hometown
Upvotes: 0
Reputation: 86808
It won't be the grouping, your joins are joining multiple records form one table, to a single record in another. This is causing your duplication.
For example, for a single entry in the user table, you may have 3 responses in reactions, and 3 responses in posts. Your query is returning 9 records, because all of that user's reactions are joined to all of that user's posts...
userid | reaction_id | post_id
1 1 1
1 1 2
1 1 3
1 2 1
1 2 2
1 2 3
1 3 1
1 3 2
1 3 3
This means that you need to aggregate the reactions separately from the posts...
SELECT
u.name, u.hometown,
r.agrees
r.disagrees,
p.compliments,
p.complaints
FROM
users AS u
LEFT JOIN
(
SELECT
userid,
SUM(IF(r.opinion="agree",1,0)) AS agrees
SUM(IF(r.opinion="disagree",1,0)) AS disagrees,
FROM
reactions
GROUP BY
userrid
)
AS r
ON r.userid = u.id
LEFT JOIN
(
SELECT
userid,
SUM(IF(p.opinion="agree",1,0)) AS compliments,
SUM(IF(p.opinion="disagree",1,0)) AS complaints
FROM
posts
GROUP BY
userid
)
AS p
ON p.userid = u.id
WHERE
u.id = 1
Upvotes: 2
Reputation: 52157
Depending on your DBMS, you might be able to do this:
SELECT
*,
(SELECT COUNT(*) FROM POSTS WHERE POSTS.userid = USERS.id and opinion = 'agree') compliments,
(SELECT COUNT(*) FROM POSTS WHERE POSTS.userid = USERS.id and opinion = 'disagree') complaints,
(SELECT COUNT(*) FROM REACTIONS WHERE REACTIONS.userid = USERS.id and opinion = 'agree') positive_reactions,
(SELECT COUNT(*) FROM REACTIONS WHERE REACTIONS.userid = USERS.id and opinion = 'disagree') negative_reactions
FROM USERS
Upvotes: 0