SQL conditions – select ID who have multiple values in a column

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 ...

Answer

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.