How do I resolve the problem with inner join in SQL

Question

0.00/5 (No votes)

See more:

I have the next query

SQL

<pre>query = '''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"
    '''
puntos = pd.read_sql(query,db)
puntos

the table is:

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

and I want to add the filter that give the players with more than 30K points and no deathDate with some award

What I have tried:

I try this

SQL

<pre>query = '''SELECT players.playerID, lastName, SUM(points) AS AllPoints, 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 SUM(points) > 30000 AND award IS NOT NULL and deathDate = "0000-00-00"
    
    '''

puntos2 = pd.read_sql(query,db)
puntos2

but this give a mix and repeat the players because for example if one player won two award the query make two time the SUM() of him points. I need the filter with a correct sum

i have this query ( it is incorrect )

	playerID	lastName	AllPoints	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
...	...	...	...	...	...
228	wilkido01	Wilkins	106672.0	All-NBA Second Team	0000-00-00
229	wilkido01	Wilkins	53336.0	All-NBA Third Team	0000-00-00
230	willibu01	Williams	33568.0	All-Defensive First Team	0000-00-00
231	willibu01	Williams	33568.0	All-Defensive Second Team	0000-00-00
232	worthja01	Worthy	32640.0	All-NBA Third Team	0000-00-00

Posted 4-Nov-22 11:46am

David Vazquez Bande

Updated 6-Nov-22 21:49pm

v2

Add a Solution

Comments

[no name] 5-Nov-22 4:03am

to avoid redundant line use 'distinct'.

and to fix the inner join ( which collapse table ), you have another way to achieve the sub-request

 select * from tbl where criteria IN (select id, ..... from table2 where criteria2 = .... )

it's equal to JOIN use , but more readable.

David Vazquez Bande 5-Nov-22 4:27am

sorry I don't understand the your query or your resolution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 7870345 · Answer 1 · 2022-11-06T21:49:00

I dont understand very well what the query means, but try

SQL

query = '''SELECT players.playerID, lastName, SUM(points) AS AllPoints, deathDate
    FROM players
    
    INNER JOIN players_teams 
    ON players.playerID = players_teams.playerID  

    WHERE award IS NOT NULL
    
    GROUP BY players.playerID, lastName, deathDate  
    
    HAVING SUM(points) > 30000 AND deathDate = "0000-00-00"
    '''
puntos = pd.read_sql(query,db)
puntos

How do I resolve the problem with inner join in SQL

1 solution

Solution 1

Add your solution here

Preview 0

How do I resolve the problem with inner join in SQL

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us