user3440145 - 6 months ago 4x

SQL Question

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

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

Source (Stackoverflow)

Comments