Reputation: 10203
How to exclude records with certain values in sql (MySQL)
Col1 Col2
----- -----
A 1
A 20
B 1
C 20
C 1
C 88
D 1
D 20
D 3
D 1000
E 19
E 1
Return Col1 (and Col2), but only if the value in Col2 is 1 or 20, but not if there's also another value (other than 1 or 20)
Desired result:
Col1 Col2
----- -----
A 1
A 20
B 1
But not C,D and E because there's a value in Col2 other than 1 or 20
I've used fictitious values for Col2 and only two values (1 and 20) but in real there some more.
I can use IN ('1', '20') for the values 1 and 20 but how to exclude if there's also another value in Col2. (there's no range !)
Upvotes: 12
Views: 51936
Reputation: 10083
You can do the same with NOT EXISTS
clause also,
Select A.COL1,A.COL2
From MYTABLE A
where NOT EXISTS
(Select COL1 from MYTABLE B where A.COL1=B.COL1 and
COL2 NOT IN (1,20)
GROUP BY COL1)
Upvotes: 0
Reputation: 37365
Use SUM()
SELECT
*
FROM
t
INNER JOIN
(SELECT
SUM(IF(Col2 IN (1, 20), 1, -1)) AS ranges,
col1
FROM
t
GROUP BY
col1
HAVING
ranges=2) as counts
ON counts.col1=t.col1
Update: while it will work for non-repeated list, it may result in wrong set for table with repeated values (i.e. 1
, 20
, 20
, 1
in column - it will still fit request if repeats are allowed, but you've not mentioned that). For case with repeats where's a way too:
SELECT
t.*
FROM
t
INNER JOIN
(SELECT
col1,
col2
FROM
t
GROUP BY
col1
HAVING
COUNT(DISTINCT col2)=2
AND
col2 IN (1, 20)) AS counts
ON test.col1=counts.col1
(and that will work in common case too, of course)
Upvotes: 1
Reputation: 28771
Select col1,col2
From table
Where col1 not in (Select col1 from table where col2 not in (1,20))
Upvotes: 15