Seong-Bae Hwang (10/18/2014)

---

Hi

If t2 has large volume of data, there would be performance issue on your approach. My approach is below.

USE tempdb;

GO

DECLARE @t TABLE (Id INT);

INSERT INTO @t

VALUES(108)

, (102)

, (103)

, (101);

IF OBJECT_ID('tempdb..#t2') IS NOT NULL

DROP TABLE #t2;

CREATE TABLE #t2

(

CategoryId INT

, Id INT

);

CREATE INDEX __#t2_CategoryId_Id ON #t2(CategoryId, Id);

CREATE INDEX __#t2_Id_CategoryId ON #t2(Id, CategoryId);

INSERT INTO #t2

VALUES(2, 50)

,(2, 51)

,(2, 101)

,(2, 59)

,(2, 60)

,(3, 101)

,(3, 102)

,(3, 103)

,(3, 108)

,(4, 109)

,(4, 120)

,(9, 125)

,(9, 103)

,(9, 101);

DECLARE @NoOfIds int = (SELECT COUNT(*) FROM @t);

WITH AllCategories

AS

(

SELECTDISTINCT T2.CategoryId

FROM#t2 T2

INNER JOIN @t T ON (T.Id = T2.Id)

)

SELECTT2.CategoryId

FROMAllCategories AC

INNER JOIN #t2 T2 ON (T2.CategoryId = AC.CategoryId)

LEFT OUTER JOIN @t T ON (T.Id = T2.Id)

GROUP BY T2.CategoryId

HAVING COUNT(T.Id) = @NoOfIds;