T-SQL How to Match Multiple Rows

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.

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

forum

T-SQL How to Match Multiple Rows

Anonymous User

1 Answers

Liked By