Logic for checking for exact identifiers in a group in SQL Server

Question

I have some sample data, for example:

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 I'm trying to check if each group FK_ID exact match of the values TYPE_ID 1 & 2 .

So, the expected result is as follows:

• (1, 10, 1) this will not work
  • As in the group FK_ID = 10 , we have only one record
• (2, 11, 1), (3, 11, 2) this should go
  • As in the group FK_ID = 11 , we have two entries.
  • And both TYPE_ID correspond to the values 1 & 2 .
• (4, 12, 1), (5, 12, 2), (6, 12, 3) this should also fail
  • Since we have 3 entries.
• (7, 14, 2), (8, 14, 3) this should also fail
  • Despite the fact that we have two entries, it does not work, since the TYPE_ID does not match the values 1 & 2 .

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 does not work as expected, because it also passes for FK_ID = 12 , which has three entries.

Demo: SQL Fiddle

Answer 1

There may be several different ways to do this. One could:

 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 upon request

 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 

Answer 3

Another way, but less optimal than Nenad, is to use SELECT INTO (with access to the temporary table), and then with another SELECT query only those rows that have the correct TYPE_ID values.