How to join 3 tables in SQL without duplicates?

Question

I have this work:

We have to get players with at least 30,000 points in their career, who have won a prize and are still alive.

And I have in my sql 3 different tables for this information.

I can obtain the players with more than 30K and still alive with this code:

SELECT players.playerID, lastName, SUM(points) AS AllPoints, deathDate, 
FROM players
INNER JOIN players_teams  ON players.playerID = players_teams.playerID  
GROUP BY players.playerID, lastName, deathDate  
HAVING SUM(points) > 30000 AND deathDate = "0000-00-00"

and I have:

playerID    lastName    AllPoints   deathDate
0   abdulka01   Abdul-Jabbar    38387.0 0000-00-00
1   ervinju01   Erving  30026.0 0000-00-00
2   jordami01   Jordan  32292.0 0000-00-00
3   malonka01   Malone  36928.0 0000-00-00

So only 4 players have these two characteristics.

But if I want the 3 tables that tell me what player have a award, it give me the wrong result.

This is my try:

SELECT players.playerID, lastName, sum(points), award, deathDate
FROM players
INNER JOIN players_teams ON players.playerID = players_teams.playerID  
INNER JOIN awards_players ON players.playerID = awards_players.playerID  
GROUP BY players.playerID, lastName, award, deathDate
HAVING award IS NOT NULL and deathDate = "0000-00-00"

But the query gives me:

playerID    lastName    sum(points) award   deathDate
0   abdulka01   Abdul-Jabbar    191935.0    All-Defensive First Team    0000-00-00
1   abdulka01   Abdul-Jabbar    230322.0    All-Defensive Second Team   0000-00-00
2   abdulka01   Abdul-Jabbar    383870.0    All-NBA First Team  0000-00-00
3   abdulka01   Abdul-Jabbar    191935.0    All-NBA Second Team 0000-00-00
4   abdulka01   Abdul-Jabbar    76774.0 Finals MVP  0000-00-00
... ... ... ... ... ...
786 wisewi01    Wise    19454.0 All-ABA Second Team 0000-00-00
787 wisewi01    Wise    19454.0 All-Defensive Team  0000-00-00
788 worthja01   Worthy  32640.0 All-NBA Third Team  0000-00-00
789 worthja01   Worthy  16320.0 Finals MVP  0000-00-00
790 youngth01   Young   4534.0  All-Rookie Second Team  0000-00-00

And as you can see, the table gives the same player repeat and the query makes a add each point for each award. For example If X have 3 awards in 1997, the query multiplies by 3 their points.

Does this answer your question? [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/questions/12464037/two-sql-left-joins-produce-incorrect-result) — philipxy, Nov 07 '22 at 02:45

GMB · Answer 1 · 2022-11-06T21:06:00.743

The problem is that a player may have multiple awards - in that case, the second join "multiplies" the rows, and the total points go off.

Since you just want to check if the player has any award, I would recommend exists:

select p.playerid, p.lastname, sum(pt.points) as allpoints, p.deathdate
from players p
inner join players_teams pt on pt.playerid = p.playerid
where 
    p.deathdate = "0000-00-00"
    and exists (select 1 from awards_players ap where ap.playerID = p.playerID)
group by p.playerid, p.lastname, p.deathdate  
having sum(pt.points) > 30000

Note that I prefixed all columns in the query with the table they belong to, which makes it easier to red. Also note that the condition on deathdate belongs to the where clause rather than to the having clause, which operates on aggregate expressions (although MySQL allows it).

We could go one sqtep further with subquerires, and use a lateral join to retrieve the total points, so there is no aggregation at all in the outer query (this requires MySQL 8.0.14 at least):

select p.playerid, p.lastname, pt.allpoints, p.deathdate
from players p
cross join lateral (
    select sum(pt.points) as allpoints 
    from players_teams pt 
    where pt.playerid = p.playerid
) p
where 
    p.deathdate = "0000-00-00"
    and exists (select 1 from awards_players ap where ap.playerID = p.playerID)
    and pt.allpoints > 30000

How to join 3 tables in SQL without duplicates?

1 Answers1