T-SQL How to Match Multiple Rows

sql, sql-server

Solution

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

Problem

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.

Original source

Related problems