Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    44

    Unanswered: Problem with duplicate SUM in JOIN

    I've tried and search the forums but can't find this answer. Let's say I have this query in table1:

    table1:
    player_id
    points

    Code:
    SELECT a.player_id, SUM( a.points ) AS points
    FROM table1 a
    GROUP BY a.player_id
    This works fine, but now I would only like those player_id's that are currently important, and these are stored in table2.

    table2:
    player_id
    team_id

    The problem is that the player_id column in table2 are not unique (imagine this is some kind of fantasy league with many teams containing the same players).

    When doing this query,

    Code:
    SELECT a.player_id, SUM( a.points ) AS points
    FROM table1 a
    JOIN table2 b ON a.player_id = b.player_id
    GROUP BY a.player_id
    the sum will be incorrect. The JOIN will multiple columns when there's duplicate player_id's in table2.

    What's the solution here?

    Appreciate any help! Thanks.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?
    Code:
    SELECT a.player_id
         , SUM( a.points ) AS points
     FROM  table1 a
     WHERE EXISTS
          (SELECT 0
            FROM  table2 b
            WHERE a.player_id = b.player_id
          )
     GROUP BY
           a.player_id

  3. #3
    Join Date
    Feb 2012
    Posts
    44
    Thanks a lot, it works great!

    What does it mean by SELECT 0?

    If I want other conditions as well, it's just to add them like this right?

    Code:
    SELECT a.player_id
         , SUM( a.points ) AS points
     FROM  table1 a
     WHERE EXISTS
          (SELECT 0
            FROM  table2 b
            WHERE a.player_id = b.player_id
          )
    AND a.season = 1
     GROUP BY
           a.player_id
    I tried that and it worked.

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •