Logic to check if exact ids are present in a group in SQL Server

Question

I have some sample data like:

INSERT INTO mytable ([ID], [FK_ID], [TYPE_ID])
VALUES
    (1, 10, 1),
    (2, 11, 1), (3, 11, 2),    
    (4, 12, 1), (5, 12, 2), (6, 12, 3),
    (7, 14, 2), (8, 14, 3)

Now, here I am trying to check if in each group by FK_ID we have exact match of TYPE_ID values 1 & 2.

So, the expected output is like:

1. (1, 10, 1) this should fail
   • As in group FK_ID = 10 we only have one record
2. (2, 11, 1), (3, 11, 2) this should pass
   • As in group FK_ID = 11 we have two records.
   • And both the TYPE_ID are matching 1 & 2 values.
3. (4, 12, 1), (5, 12, 2), (6, 12, 3) this should also fail
   • As we have 3 records here.
4. (7, 14, 2), (8, 14, 3) this should also fail
   • Even though we have exact two records, it should fail as the TYPE_ID here are not matching with 1 & 2 values.

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)
              group by t2.FK_ID 
              having count(t2.TYPE_ID) = 2);

This is not working as expected, because it also pass for FK_ID = 12 which has three records.

Demo: SQL Fiddle

Answer 1

There are probably several different ways of doing this. One could be:

SELECT FK_ID 
FROM mytable
GROUP BY FK_ID
HAVING COUNT(*) = 2
AND MIN(TYPE_ID) = 1
AND MAX(TYPE_ID) = 2

Answer 2

We can add min and max to the group by query

select t1.* from mytable t1,
 ( select fk_id, count(*) As cnt from mytable  

              Group by fk_id
              Having count(*) = 2
                AND max(type_id)=2
                ANd min(Type_id) = 1) As t2
 Where t1.fk_id = t2.fk_id