Home > DeveloperSection > Forums > T-SQL How to Match Multiple Rows
Simons Hood
Simons Hood

Total Post:70

Posted on    May-25-2015 7:40 AM

 MSSQL Server SQL Server 2008 

 1 Reply(s)
 431  View(s)
Rate this:

In my sample application two tables company and items in my case company contains multiple items. We need to recognize multiple products whether they can match a company which is already in items record some script are below.

DECLARE @tblcompany TABLE(

    companyID   int,

    ItemID   int


INSERT INTO @tblcompany VALUES(436, 4313)

INSERT INTO @tblcompany VALUES(436, 4305)

INSERT INTO @tblcompany VALUES(436, 4986)


INSERT INTO @tblcompany VALUES(437, 4313)

INSERT INTO @tblcompany VALUES(437, 4305)

INSERT INTO @tblcompany VALUES(442, 4313)

INSERT INTO @tblcompany VALUES(442, 4335)

INSERT INTO @tblcompany VALUES(445, 4305)

INSERT INTO @tblcompany VALUES(445, 4335)



    ItemtID   int


INSERT INTO @tblitem VALUES(4305)

INSERT INTO @tblitem VALUES(4313)


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

Brad Pitt
Brad Pitt

Total Post:61

Posted on    May-25-2015 8:24 AM

Try this code

Declare @cnt Int

Select @cnt = count(distinct itemID) from tblitem


SELECT B.companyid

FROM   (SELECT companyid

        FROM   tblcompany

        GROUP  BY companyid

        HAVING Count(itemid) = @cnt) A

       JOIN tblcompany B

         ON a.companyid = b.companyid

WHERE  EXISTS (SELECT 1  FROM tblitem c WHERE c.itemid = b.itemid)

GROUP  BY B.companyid

HAVING Count(DISTINCT B.itemid) = @cnt

Don't want to miss updates? Please click the below button!

Follow MindStick