CODEWITHSUNDEEP
sql
Tim
Tim

Reputation: 2411

SQL EXCEPT clause and its behavior

I'm currently trying to run this block of code:

SELECT DISTINCT P.PlayerID
FROM Players as P, AllStar as A
WHERE P.PlayerID = A.PlayerID
GROUP BY P.PlayerID
HAVING COUNT(a.GamesPlayed) >= 8 // each GamesPlayed is always = 1

EXCEPT
SELECT DISTINCT P.PlayerID
FROM Players as P, HallOfFame as HOF
WHERE P.PlayerID = HOF.hofID OR P.Year BETWEEN 2010 AND 2017;

To find all basketball players that have been in at least 8 AllStar games, are not in the hall of fame, and whose last years of play are before 2010. Unfortunately, my current block of code isn't giving me the right result but it seems to me that my logic check outs here. Could someone help me identify what I'm doing incorrectly here?

Thanks so much!

Upvotes: 0

Views: 53

Answers (2)

D Stanley
D Stanley

Reputation: 152521

The WHERE clause in your EXCEPT clause looks wrong:

WHERE P.PlayerID = HOF.hofID OR P.Year BETWEEN 2010 AND 2017;

I think you want a LEFT JOIN instead:

EXCEPT
SELECT DISTINCT P.PlayerID
FROM Players as P
LEFT JOIN  HallOfFame as HOF
ON P.PlayerID = HOF.hofID 
WHERE HOF.hofID IS NOT NULL OR P.Year BETWEEN 2010 AND 2017;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269613

I would not think of solving this using EXCEPT. To me, this is an aggregation query with a WHERE filter:

SELECT P.PlayerID
FROM Players P JOIN
     AllStar A
     ON P.PlayerID = A.PlayerID
WHERE NOT EXISTS (SELECT 1 FROM HallOfFame HOF WHERE P.PlayerID = HOF.hofID)
GROUP BY P.PlayerID
HAVING COUNT(a.GamesPlayed) >= 8 AND
       MAX(P.Year) < 2010;

Upvotes: 1

Related Questions