Find Exact Match For a Group Items


--Both AMB and Peso provided solutions for this question 
--but I developed a solution with binary calculation (just post here for future reference)
--Thanks for AMB for the sample table data from the question
 
DECLARE @P TABLE (PId int NOT NULL UNIQUE);
DECLARE @C TABLE (PId int NOT NULL, keyword varchar(25) NOT NULL, UNIQUE (PId, keyword));

INSERT INTO @P VALUES(1),(2),(3);

INSERT INTO @C VALUES(1, 'one'),(1, 'two'),(1, 'three'),(1, 'four');
INSERT INTO @C VALUES(2, 'one'),(2, 'two'),(2, 'three');
INSERT INTO @C VALUES(3, 'one'),(3, 'two');

--Peso's compact solution
SELECT   PId
FROM  	  @C
GROUP BY  PId
HAVING   MIN(CASE WHEN Keyword IN ('one', 'two', 'three') THEN 1 ELSE 0 END) = 1   -- No extra record outside the "scope"
      AND SUM(CASE WHEN Keyword IN ('one', 'two', 'three') THEN 1 ELSE 0 END) = 3 -- All records should match



--My approach
	   
Select pid 
FROM  @C 
group by pid
Having (sum( Cast(CAST(keyword AS BINARY(4)) as bigint)) - (Cast(CAST('one' AS BINARY(4)) as bigint)+ Cast(CAST('two' AS BINARY(4)) as bigint)+ Cast(CAST('three' AS BINARY(4)) as bigint))  ) = 0


select  Cast(CAST('one' AS BINARY(4)) as bigint),Cast(CAST('two' AS BINARY(4)) as bigint), Cast(CAST('three' AS BINARY(4)) as bigint)   



 

https://social.msdn.microsoft.com/Forums/en-US/5e52baf8-8640-4474-beb4-7b286af081a8/select-parent-ids-that-have-exact-match-on-child-values-no-more-no-less?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s