How to get common value for two records from msSQL

catId  DealId
 1      668
 2      668
 3      669
 1      669
 3      671
11      671
12      671
 7      669
12      672
13      673

Suppose it's a table. If I give two values for catId as input, a query should return all the records that c开发者_StackOverflowontain same DealId.

SQL Statement

;WITH CatIDs (catID) AS (
    SELECT  1
    UNION ALL SELECT 2
)   
SELECT  mt.*
FROM    MyTable mt
        INNER JOIN CatIDs cid ON cid.catID = mt.catID
        INNER JOIN (
            SELECT  mt.DealID
            FROM    MyTable mt
                    INNER JOIN CatIDs ids ON ids.catID = mt.catID
            GROUP BY
                    mt.DealID
            HAVING  COUNT(mt.DealID) = (SELECT COUNT(*) FROM CatIDs)
        ) did ON did.DealID = mt.DealID

Test script

;WITH MyTable (catID, DealID) AS (
    SELECT 1, 668
    UNION ALL SELECT 2, 668
    UNION ALL SELECT 3, 669
    UNION ALL SELECT 1, 669
    UNION ALL SELECT 3, 671
    UNION ALL SELECT 11, 671
    UNION ALL SELECT 12, 671
    UNION ALL SELECT 7, 669
    UNION ALL SELECT 12, 672
    UNION ALL SELECT 13, 673
)
, CatIDs (catID) AS (
    SELECT  1
    UNION ALL SELECT 2
)   
SELECT  mt.*
FROM    MyTable mt
        INNER JOIN CatIDs cid ON cid.catID = mt.catID
        INNER JOIN (
            SELECT  mt.DealID
            FROM    MyTable mt
                    INNER JOIN CatIDs ids ON ids.catID = mt.catID
            GROUP BY
                    mt.DealID
            HAVING  COUNT(mt.DealID) = (SELECT COUNT(*) FROM CatIDs)
        ) did ON did.DealID = mt.DealID

Try this (hopping that's what you need):

SELECT DealId, catId, count(catId) AS tot
FROM your_table
WHERE catId = your_cat1 OR catId = your_cat2
GROUP BY DealId
HAVING tot > 1

I don't understand your question,hope bellow syntax help you . CREATE PROCEDURE [spName]

    @Parameter1 INT,
    @Parameter2 INT

AS 
    BEGIN              

        SELECT * FROM TableName WHERE 
        DealId    FROM    TableName WHERE catId=@Parameter1
        AND 
        DealId    FROM    TableName WHERE catId=@Parameter2
     END