CODEWITHSUNDEEP
sqloracle-databaseselect
mediii
mediii

Reputation: 97

Select all values matching several values in second column

I have a table like this:

    ID       VALUE

--------------------
     A   |    abc       <---  
--------------------
     A   |    def       <---
--------------------
     A   |    ghi       <---
--------------------
     B   |    abc       x
--------------------
     C   |    abc       x
--------------------
     C   |    def       x
--------------------
     C   |    xyz       x
--------------------       
     D   |    abc       <---
--------------------
     D   |    def       <---
--------------------
     D   |    ghi       <---

I'd like to select all IDs where an ID is matching all the values (abc, def AND ghi)

Result should be 

A 
D

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You can use conditional aggregation:

select id
from t
where value in ('abc', 'def', 'ghi')
group by id
having count(*) = 3;

If you can have duplicate id/value pairs, use count(distinct id) instead of count(*).

If you have only three values in the table, you can dispense with the where. Or, if you want all values in the table:

select id
from t
group by id
having count(*) = (select count(distinct value) from t)

Upvotes: 2

Related Questions