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

Question

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.

Mureinik · Accepted Answer · 2017-10-26 16:45:17Z

4

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

edited Oct 26, 2017 at 16:45

answered Oct 26, 2017 at 16:43

Mureinik

316k54 gold badges401 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SS_DBA Over a year ago

I like your solution. +1. I would have done subquery in the Where clause.

Phantom Over a year ago

Thank you so much! Works great!!

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related