CODEWITHSUNDEEP
sqlconditional-statements
userit1985
userit1985

Reputation: 1003

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

Having this COLORS table:

enter image description here

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

Upvotes: 0

Views: 2202

Answers (3)

ZairaRosas
ZairaRosas

Reputation: 27

SELECT ID
 FROM COLORS
 WHERE COLOR LIKE 'RED'
 OR COLOR LIKE 'PINK'

If you want more colors:

SELECT ID
 FROM COLORS
 WHERE COLOR LIKE 'RED'
 OR COLOR LIKE 'PINK'
 OR COLOR LIKE 'BLACK'

Upvotes: 0

m.antkowicz
m.antkowicz

Reputation: 13581

You can use HAVING clause like

SELECT ID FROM COLORS GROUP BY ID HAVING COUNT(*) > 1

You can read more here: https://www.w3schools.com/sql/sql_having.asp

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270191

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.

Upvotes: 1

Related Questions