CODEWITHSUNDEEP
mysqlsql
John Doe
John Doe

Reputation: 10213

How to exclude records with certain values in sql

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: 51938

Answers (3)

Mariappan Subramanian
Mariappan Subramanian

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)

liveDemo

Upvotes: 0

Alma Do
Alma Do

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

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

Select col1,col2
From table
Where col1 not in (Select col1 from table where col2 not in (1,20))

Upvotes: 15

Related Questions