All geek questions in one place

How to select one row based on similar or opposite data in other rows?

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

What does my table look like. I want to select player_id with nt_caps = "0" for each nat OR player_id that has nt_caps! = "0" only for nat = "68".

I am trying to use a SQL query:

 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 have one record that matches the request, but I don't want them because they have nt_caps for a different nat than nat "68".

I hope you understand what I'm trying to achieve.

+4
source share
2 answers
 SELECT * FROM x WHERE nt_caps = 0 OR (nt_caps != 0 AND nat = 68)
+2
source

The first subquery gets the rows where all nt_caps are 0, and the second subquery returns the other rows:

 (SELECT x1.player_id FROM x x1 LEFT JOIN x x2 ON x2.player_id = x1.player_id AND x2.nt_caps <> 0 WHERE x1.nt_caps = 0 AND x2.id IS NULL) UNION (SELECT x1.player_id FROM x x1 LEFT JOIN x x2 ON x2.player_id = x1.player_id AND x2.nt_caps <> 0 AND x2.nat <> x1.nat WHERE x1.nat = 68 AND x1.nt_caps <> 0 // Exclude this line if nt_caps "can be" !=0 instead of "must be" AND x2.id IS NULL)
+1
source

Source: https://habr.com/ru/post/1396288/

More articles:


All Articles