CODEWITHSUNDEEP
sql-server
Bhupendra Shukla
Bhupendra Shukla

Reputation: 3914

How to find group of rows of a table that match in another table in SQL?

I have two tables in SQL Server,

Declare @Table1 Table ( TID1 INT, TP1 INT)

Declare @Table2 Table ( TID2 INT, TP2 INT)


INSERT INTO @Table1 (TID1,TP1) VALUES (100,1)
INSERT INTO @Table1 (TID1,TP1) VALUES (100,2)
INSERT INTO @Table1 (TID1,TP1) VALUES (100,3)

INSERT INTO @Table2 (TID2,TP2) VALUES (101,1)
INSERT INTO @Table2 (TID2,TP2) VALUES (101,2)
INSERT INTO @Table2 (TID2,TP2) VALUES (101,3)
INSERT INTO @Table2 (TID2,TP2) VALUES (102,1)
INSERT INTO @Table2 (TID2,TP2) VALUES (102,2)
INSERT INTO @Table2 (TID2,TP2) VALUES (103,1)
INSERT INTO @Table2 (TID2,TP2) VALUES (103,2)
INSERT INTO @Table2 (TID2,TP2) VALUES (103,3)
INSERT INTO @Table2 (TID2,TP2) VALUES (103,4)
INSERT INTO @Table2 (TID2,TP2) VALUES (104,2)
INSERT INTO @Table2 (TID2,TP2) VALUES (105,3)

Having Data as :

TID1        TP1
----------- -----------
100         1
100         2
100         3


TID2        TP2
----------- -----------
101         1
101         2
101         3
102         1
102         2
103         1
103         2
103         3
103         4
104         2
105         3

I want to select those records which having exact matching of TP1 column in Table2 TP2 column. EX TID2 having ID 101 will be only in result set

Upvotes: 2

Views: 58

Answers (3)

gotqn
gotqn

Reputation: 43636

-- you can calculated this in CTEW or sub-query if you do not like to be in variable
DECLARE @MaxRowsCount INT = (SELECT COUNT(*) FROM @Table1);

SELECT T2.[TID2]
FROM @Table2 T2
LEFT JOIN @Table1 T1
    ON T2.[TP2] = T1.[TP1]
GROUP BY T2.[TID2]
HAVING
(
    -- current count of rows should be the same as the row count from the first table
    COUNT(T2.[TP2]) = @MaxRowsCount
)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

SELECT t2.TID2
FROM @Table2 t2
LEFT JOIN @Table1 t1
    ON t2.TP2 = t1.TP1
GROUP BY t2.TID2
HAVING SUM(CASE WHEN t1.TP1 IS NULL THEN 1 ELSE 0 END) = 0 AND
       COUNT(*) = (SELECT COUNT(*) FROM @Table1)

Upvotes: 3

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

Try Like below. 

SELECT TID2
FROM   @TABLE1 T
       RIGHT JOIN  @TABLE2 T2
         ON T.TP1 = T2.TP2
GROUP  BY TID2
HAVING COUNT(T2.TP2) = (SELECT COUNT(*) FROM @TABLE1)

Upvotes: 0

Related Questions