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

Member 15627495 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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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