T-SQL HOW TO MATCH MULTIPLE ROWS

Simons Hood

Total Post:70

Points:494
Posted by  Simons Hood
 703  View(s)
Ratings:
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.

  1. Brad Pitt

    Post:61

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

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!