CODEWITHSUNDEEP
phpsqljoin
Diederik
Diederik

Reputation: 612

SQL Join with three tables

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

Answers (4)

Bijit
Bijit

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

Mosty Mostacho
Mosty Mostacho

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

MatBailie
MatBailie

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

Branko Dimitrijevic
Branko Dimitrijevic

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

Related Questions