conditional statements - SQL conditions - select ID who have multiple values in a column

Question

Having this COLORS table:

How do I select the ID who have both COLOR RED & PINK? (Could be more than 2 colors)

 SELECT ID
 FROM COLORS
 WHERE COLOR IN ('RED')
 AND ...

question from:https://stackoverflow.com/questions/65910528/sql-conditions-select-id-who-have-multiple-values-in-a-column

Answer 1

A simple method is aggregation and having:

select id
from colors c
where color in ('RED', 'PINK')
group by id
having count(*) = 2;

This assumes that your original data has no duplicates. If that is the case, use count(distinct color) = 2.