CODEWITHSUNDEEP
mysqlsqlgroup-by
Waneck
Waneck

Reputation: 2470

Selecting a GROUP BY statement only when a predicate is true on all grouped fields

I have a table like this:

-------------
| id  |  A  |
-------------
|  1  |  2  |
|  1  |  5  |
|  1  |  6  |
|  2  |  6  |
|  2  |  9  |
-------------

How can I select all ids that have values of A that don't contain certain numbers, like I don't want any id that have A IN (9,-1,20), for example?

Thank you!

Upvotes: 1

Views: 703

Answers (1)

Benoit
Benoit

Reputation: 79205

Supposing you want only ID 1 because for ID 2 there is a value of A which is IN (9,-1,20) this is what you want:

SELECT t1.id, t1.A
  FROM my_table t1
  LEFT JOIN my_table t2
    ON t2.id = t1.id
   AND t2.A IN (9, -1, 20)
 WHERE t2.id IS NULL

or

SELECT id
  FROM my_table t1
 WHERE NOT EXIST (
       SELECT NULL
         FROM my_table t2
        WHERE t2.id = t1.id
          AND t2.A IN (9, -1, 20)
       )

Upvotes: 2

Related Questions