Logic to check for exact identifiers (3+ records) in a group in SQL Server

Question

I have some sample data, for example:

INSERT INTO mytable
    ([FK_ID], [TYPE_ID])
VALUES
    (10, 1),
    (11, 1), (11, 2),    
    (12, 1), (12, 2), (12, 3),
    (14, 1), (14, 2), (14, 3), (14, 4),
    (15, 1), (15, 2), (15, 4)

Now I'm trying to check if each group has an FK_IDexact match of the TYPE_ID values ​​for 1, 2 & 3.

So, the expected result is as follows:

• (10, 1) it will not work
  • As in the group FK_ID = 10, we have only one entry
• (11, 1), (11, 2) it should also fail
  • As in the group FK_ID = 11, we have two entries.
• (12, 1), (12, 2), (12, 3) it must pass
  • As in the group FK_ID = 12, we have two entries.
  • And everything TYPE_IDexactly matches the values 1, 2 & 3.
• (14, 1), (14, 2), (14, 3), (14, 4) it should also fail
  • Since we have 4 entries.
• (15, 1), (15, 2), (15, 4) it should also fail
  • Despite the fact that we have three entries, this should fail, since TYPE_IDhere (1, 2, 4) do not correspond to the required coincidence (1, 2, 3).

Here is my attempt:

select * from mytable t1
where exists (select COUNT(t2.TYPE_ID) 
          from mytable t2 where t2.FK_ID = t1.FK_ID
          and t2.TYPE_ID IN (1, 2, 3)
          group by t2.FK_ID having COUNT(t2.TYPE_ID) = 3);

, , FK_ID = 14, .

: SQL Fiddle

, , , 4 TYPE_ID, (1,2,3,4) (1,2,3,4,5), , .

Answer 1

, :

select fk_id
from t
group by fk_id
having sum(case when type_id in (1, 2, 3) then 1 else 0 end) = 3 and
       sum(case when type_id not in (1, 2, 3) then 1 else 0 end) = 0;

, ( , , , from (select distinct * from t) t).

"genericness", in 3.

:

with vals as (
      select id
      from (values (1), (2), (3)) v(id)
     )
select fk_id
from t
group by fk_id
having sum(case when type_id in (select id from vals) then 1 else 0 end) = (select count(*) from vals) and
       sum(case when type_id not in (select id from vals) then 1 else 0 end) = 0;

Answer 2

:

SELECT y.fk_id FROM
    (SELECT x.fk_id, COUNT(x.type_id) AS count, SUM(x.type_id) AS sum 
    FROM mytable x GROUP BY (x.fk_id)) AS y
WHERE y.count = 3 AND y.sum = 6

, y.count N y.sum N*(N-1)/2, N - , (1, 2, ..., N).

Answer 3

You can try this query. COUNTand DISTINCTused to eliminate duplicate entries.

SELECT 
    [FK_ID]
FROM 
    @mytable T 
GROUP BY 
    [FK_ID]
HAVING
    COUNT(DISTINCT CASE WHEN [TYPE_ID] IN (1,2,3) THEN [TYPE_ID] END) = 3
    AND COUNT(CASE WHEN [TYPE_ID] NOT IN (1,2,3) THEN [TYPE_ID] END) = 0

Answer 4

Try the following:

 select FK_ID,count(distinct TYPE_ID) from mytable
 where TYPE_ID<=3
 group by FK_ID
 having count(distinct TYPE_ID)=3

Answer 5

You should use CTEwith the Dynamicpass value that you mentioned in Q.

WITH CTE
     AS (
     SELECT FK_ID,
            COUNT(*) CNT
     FROM #mytable
     GROUP BY FK_ID
     HAVING COUNT(*) = 3) <----- Pass Value here What you want to Display Result,
     CTE1
     AS (
     SELECT T.[ID],
            T.[FK_ID],
            T.[TYPE_ID],
            ROW_NUMBER() OVER(PARTITION BY T.[FK_ID] ORDER BY
                             (
                                 SELECT NULL
                             )) RN
     FROM #mytable T
          INNER JOIN CTE C ON C.FK_ID = T.FK_ID),
     CTE2
     AS (
     SELECT C1.FK_ID
     FROM CTE1 C1
     GROUP BY C1.FK_ID
     HAVING SUM(C1.TYPE_ID) = SUM(C1.RN))
     SELECT TT1.*
     FROM CTE2 C2
          INNER JOIN #mytable TT1 ON TT1.FK_ID = C2.FK_ID;

Top SQLCommand that will lead to the result (I passed 3):

ID  FK_ID   TYPE_ID
4   12      1
5   12      2
6   12      3