CODEWITHSUNDEEP
sqlsql-serverjoin
nawlrus
nawlrus

Reputation: 797

SUMing rows in two different tables in a nested join. SQL Server

I have the following tables:

Users, Beats, Flows, Beat_Likes_Dislikes, Flow_Likes_Dislikes. Here are the relations.

Users UserID is a primary key that has a relationship with both Beats and FLow's UserID foreign key. Beats BeatID has a relationship with Beats_Likes_Dislikes BeatID. Its the same with flows and its like_dislike table. I need to sum the Likes in Beats and Flows as Likes and the Dislikes in both tables as Dislikes. I have the following:

SELECT
ISNULL(SUM(Beats_Likes_Dislikes.[Like]) , 0) AS Likes, 
-- + ISNULL(SUM(Flows_Likes_Dislikes.[Like]), 0) AS Likes,
ISNULL(SUM(Beats_Likes_Dislikes.Dislike), 0) AS DisLikes 
-- + ISNULL(SUM(Flows_Likes_Dislikes.DisLike), 0) AS DisLikes
From Users 
INNER JOIN Beats 
ON Users.UserID = Beats.UserID
INNER JOIN Beats_Likes_Dislikes ON Beats.BeatID= Beats_Likes_Dislikes.BeatID
--INNER JOIN Flows ON Users.UserID = Flows.UserID
--INNER JOIN Flows_Likes_Dislikes ON Flows.FlowID=Flows_Likes_Dislikes.FlowID
Where Users.UserID = '110'

This works just great returning the sum of the likes and dislikes (separate rows) for beats. I need to get the same data from the flows table. You see that I have commented out the portion for the flows table because the numbers were not adding up. What am I doing wrong here?

Thanks.

Upvotes: 0

Views: 285

Answers (3)

davesnitty
davesnitty

Reputation: 1850

Your numbers may be off due to having inadvertent many-to-many joins. For instance, every unique user can have multiple beats, and each unique beat can have multiple likes or dislikes. That is ok, but if you then try to horizontally join another set of tables, you wind up creating too many rows, as the ON condition is true more than once for each user/flow/flowrating pair.

To rectify this, you may want to separate into 2 queries and then join the results

select 
coalesce(beat_likes,0)+coalesce(flow_likes,0) as total_likes,
coalesce(beat_dislikes,0)+coalesce(flow_dislikes,0) as total_dislikes
FROM
(    
SELECT 
u.UserID,
SUM(bld.Like) as beat_likes,
SUM(bld.Dislike) as beat_dislikes 
From Users u
    INNER JOIN Beats b
    ON u.UserID = b.UserID
        INNER JOIN Beats_Likes_Dislikes bld
        ON b.BeatID= bld.BeatID
Where u.UserID = '110'
GROUP BY u.UserID
) t1
JOIN 
(
SELECT 
u.UserID,
SUM(fld.Like) as flow_likes,
SUM(fld.Dislike) as flow_dislikes 
From Users u
    INNER JOIN Flows f
    ON u.UserID = f.UserID
        INNER JOIN Flows_Likes_Dislikes fld
        ON f.FlowID= fld.FlowID
Where u.UserID = '110'
GROUP BY u.UserID
) t2
on t1.UserID =t2.UserID

Upvotes: 1

Steve Stedman
Steve Stedman

Reputation: 2672

Give this a try:

SELECT SUM(likes) as totalLikes, 
       SUM(dislikes) as totalDislikes 
  FROM (SELECT Isnull(SUM(beats_likes_dislikes.[Like]), 0)  AS likes, 
               Isnull(SUM(beats_likes_dislikes.dislike), 0) AS dislikes 
          FROM users 
         INNER JOIN beats 
               ON users.userid = beats.userid 
         INNER JOIN beats_likes_dislikes 
               ON beats.beatid = beats_likes_dislikes.beatid 
        WHERE  users.userid = '110' 

        UNION ALL 

        SELECT Isnull(SUM(flows_likes_dislikes.[Like]), 0)  AS likes, 
               Isnull(SUM(flows_likes_dislikes.dislike), 0) AS dislikes 
          FROM users 
         INNER JOIN flows 
               ON users.userid = flows.userid 
         INNER JOIN flows_likes_dislikes 
               ON flows.flowid = flows_likes_dislikes.flowid 
         WHERE users.userid = '110') AS t

It should give you the total likes and dislikes for the user.

Upvotes: 0

bumble_bee_tuna
bumble_bee_tuna

Reputation: 3563

Depending on your exact requirements you probably need to use a LEFT OUTER JOIN as opposed to an INNER JOIN. Info on Join Types ... Thats why your numbers are off.

Upvotes: 0

Related Questions