T-SQL How to Match Multiple Rows

Total Post:70

 984  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.

  1. Post:61

    Re: T-SQL How to Match Multiple Rows

    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

      Modified On Apr-09-2018 11:29:39 PM