user3440145 user3440145 - 7 months ago 10
SQL Question

Find distinct elements that match multiple values from the same column

Lets say this is the table I am talking about

id pId fId
1 1 1
2 2 1
3 2 2
4 3 2


I need to get a list of pId's who have a match to ALL of the given indices in a list of fId's.

What I mean is ->

Consider the list of fId's to be:

(1,2)


Then the result should be

2


Because only pId 2 has a match to all given entries in the list of fId's (which would be 1 and 2).

I couldn't find any way to do it so far - any help is highly appreciated :-)

vkp vkp
Answer

Aggregate on pid column and use a having clause.

select pid
from tablename
group by pid
having sum(case when fid in (1,2) then 1 else 0 end) >= 2