T-SQL How to Match Multiple Rows

Question

There are two tables, package table and product table. In my case, the package contains multiple products. We need to recognize multiple products whether they can match a package which is already in package records. Some scripts are below.

DECLARE @tblPackage TABLE(
    PackageID   int,
    ProductID   int
)

INSERT INTO @tblPackage VALUES(436, 4313)
INSERT INTO @tblPackage VALUES(436, 4305)
INSERT INTO @tblPackage VALUES(436, 4986)

INSERT INTO @tblPackage VALUES(437, 4313)
INSERT INTO @tblPackage VALUES(437, 4305)

INSERT INTO @tblPackage VALUES(442, 4313)
INSERT INTO @tblPackage VALUES(442, 4335)

INSERT INTO @tblPackage VALUES(445, 4305)
INSERT INTO @tblPackage VALUES(445, 4335)

DECLARE @tblProduct TABLE(
    ProductID   int
)

INSERT INTO @tblProduct VALUES(4305) 
INSERT INTO @tblProduct VALUES(4313)

We have two product 4305 and 4313, then I need to retrieve the matched package record 437. Only the exactly matched one can be return, so package 436 is not the right one. It's not easy to make a multiple rows query clause. please someone can have any suggestions? Thanks.

Answer 1

Try this. SQLFIDDLE DEMO

Declare @cnt Int
Select @cnt = count(distinct ProductID) from tblProduct 

SELECT B.packageid 
FROM   (SELECT packageid 
        FROM   tblpackage 
        GROUP  BY packageid 
        HAVING Count(productid) = @cnt) A 
       JOIN tblpackage B 
         ON a.packageid = b.packageid 
WHERE  EXISTS (SELECT 1  FROM tblproduct c WHERE c.productid = b.productid) 
GROUP  BY B.packageid 
HAVING Count(DISTINCT B.productid) = @cnt