T-SQL HOW TO MATCH MULTIPLE ROWS

Simons Hood

Total Post:70

Points:494
Posted by  Simons Hood
 843  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

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

Answer

NEWSLETTER

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