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

Question

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

Answers 5
Added at 2016-12-26 05:12
Tags
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?

Answers to

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

nr: #1 dodano: 2016-12-26 05:12

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

nr: #2 dodano: 2016-12-26 05:12

If you can pass the IN operator values as Set, then you can do this as below

Schema:

SELECT * INTO #TAB FROM (
SELECT 1 ID, 'a' FK
UNION ALL
SELECT 1, 'b'
UNION ALL
SELECT 1, 'c'
UNION ALL
SELECT 2, 'a'
UNION ALL
SELECT 2, 'b'
UNION ALL
SELECT 2, 'd'
UNION ALL
SELECT 1, 'a'
)AS A

Used CTE to make 'a','c' as Set

;WITH CTE AS (
SELECT 'a' FK   --Here 'a','c' passed as a Set through CTE
UNION 
SELECT 'c'
)
,FINAL AS(

SELECT DENSE_RANK() OVER (PARTITION BY ID ORDER BY (FK))AS COUNT_ID, ID, FK 
FROM #TAB where FK  IN (select FK FROM CTE)

)
SELECT ID FROM FINAL WHERE COUNT_ID>=(SELECT COUNT( FK) FROM CTE)
nr: #3 dodano: 2016-12-26 06:12

:list is the input list (bind variable). The difference of length() return values is the number of commas in the bind variable. This query, or something very close to it, should work in pretty much any DB product. Tested in Oracle.

select   pk_ref
from     tbl    --  enter your table name here
where    ',' || :list || ','   like   '%,' || fk || ',%'
group by pk_ref
having   count(distinct fk) = 1 + length(:list) - length(replace(:list, ',', ''))
nr: #4 dodano: 2016-12-26 10:12
Select pk_ref where fk='a' and pk_ref in (select pk_ref where fk='c' from yourtable) from yourtable;  

or

select pk_ref where fk='a' from yourtable intersect select pk_ref where fk='c' from yourtable;
nr: #5 dodano: 2016-12-26 18:12
    DECLARE @inputVariable VARCHAR(200) =  'a,b,c,d'
    DECLARE @inputValue INT
    DECLARE @tblInput TABLE
    (
        FK VARCHAR(100)
    )




    INSERT INTO @tblInput
    SELECT SUBSTRING( @inputVariable+',',RN,1)
      FROM (SELECT TOP 100 ROW_NUMBER() OVER(ORDER BY s.object_id) RN
      FROM sys.objects s)  s
      where LEN(@inputVariable) >= RN
      AND SUBSTRING(','+ @inputVariable,RN,1) = ','

    SELECT @inputValue = COUNT(1) FROm @tblInput

    --@inputVariable

    DECLARE @tbl TABLE 
    (
        ID INT,
        FK VARCHAR(100) 
    )

    INSERT INTO @tbl
    SELECT 1 ID, 'a' FK
    UNION ALL
    SELECT 1, 'b'
    UNION ALL
    SELECT 1, 'c'
    UNION ALL
    SELECT 2, 'a'
    UNION ALL
    SELECT 2, 'b'
    UNION ALL
    SELECT 2, 'd'
    UNION ALL
    SELECT 1, 'a'

    SELECT t.ID ,COUNT(DISTINCT t.FK) 
      FROM @tbl t
    INNER JOIn @tblInput  ti
            ON t.FK = ti.FK
        GROUP BY ID
        HAVING COUNT(DISTINCT t.FK) = @inputValue
Source Show
◀ Wstecz