CODEWITHSUNDEEP
mysqlsqlselect
Phantom
Phantom

Reputation: 688

MySQL: Select duplicate entries of first column depending on second column

I have this table (very simplified):

 id | product
------------
  1 | a
  2 | b
  3 | b
  3 | c
  4 | c
  5 | b
  5 | c
  5 | d
  6 | d
  6 | c
  7 | b
  8 | b
  9 | c
 10 | d

I would like to get all ids which have only produkt 'd' or 'c'. For example '5' should not be selected, because it also has product 'b'. The result should be: 4, 6, 9 and 10.

Upvotes: 1

Views: 35

Answers (1)

Mureinik
Mureinik

Reputation: 311308

One easy way of approaching such problems is counting the number of forbidden products an ID has:

SELECT   id
FROM     mytable
GROUP BY id
HAVING   COUNT(CASE WHEN product NOT IN ('c', 'd') THEN 1 END) = 0

Upvotes: 4

Related Questions