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

Total Post:70

Points:494
Posted on    May-25-2015 7:40 AM

 MSSQL Server SQL Server 2008 
Ratings:


 1 Reply(s)
 452  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)

 

DECLARE @tblitem TABLE(

    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

Points:429
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