CODEWITHSUNDEEP
mysqlsql
user3440145
user3440145

Reputation: 843

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 :-)

Upvotes: 0

Views: 32

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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

Upvotes: 2

Related Questions