SQL select

[kalle123]

id 	player_id 	nat 	nt_caps
13740 	28664 	97 	24 	
13741 	28664 	68 	0 	
13742 	28664 	79 	0 	
16252 	42904 	15 	40 	
16253 	42904 	68 	0 	
16254 	42904 	241 	0 	

 

That's how my table looks. I want to select the player_id's that have either nt_caps = "0" for every nat OR player_id's that have nt_caps != "0" only for nat = "68".

 

The SQL query I try to use is:

 

SELECT player_id FROM x WHERE nat = '68' AND (nat != '68' AND nt_caps = '0')

 

But then I get player_id '42904' and '28664' because they both have 1 entry that matches the query but I don't want them because they have nt_caps for another nat than nat "68".

 

I hope you understand what I try to achieve.

[requinix]

SELECT player_id FROM x WHERE
-- "all the nt_caps=0" also means "there aren't any nt_caps!=0"
player_id NOT IN (
	-- these are players to *exclude*
	SELECT player_id FROM x WHERE nt_caps != 0
	-- however not all nt_caps!=0 are bad - only the ones that aren't nat=68 too
	AND nat != 68
)