How to select a primary key which has exact foreign keys matches a given list of values?

Question

For example:

pk_ref    fk
======    ===
1         a
1         b
1         c
2         a
2         b
2         d

How do I do a query like the "pseudo" query:

select distinc pk_ref
where fk in all('a', 'c');

The return query result must match all given values for the foreign key in the list.

The result should be:

1

While the following select must not return any records.

select distinc pk_ref
where fk in all('a', 'c', 'd');

How do I do that?

Answer 1

Try this

select pk_ref 
from yourtable 
group by pk_ref 
having count(case when fk = 'a',  then 1 end) >= 1 
and count(case when fk = 'c' then 1 end) >= 1

To do it dynamically. (considering you are using SQL SERVER)

Create a split string function and pass the input as comma separated values

Declare @input varchar(8000)= 'a,c',@cnt int 

set @cnt = len(@input)-len(replace(@input,',','')) + 1

select pk_ref 
from yourtable 
Where fk in (select split_values from udf_splitstring(@input , ','))
group by pk_ref 
having count(Distinct fk) >= @cnt 

You can create a split string function from the below link

https://sqlperformance.com/2012/07/t-sql-queries/split-strings