SQLTeam.com | Weblogs | Forums

Select based on other select

Hi,

Consider a table, the first column with ID numbers, the second column with a numbers 1 to 15. Both columns may have multiple identical values.
I want to select each dataset
that has a 3 in the second column
and that has an ID number for which you can find a 1 in the second column.

So in example these datasets
11115, 1, ...
11115, 3, ...
11115, 3, ...
would show:
11115, 3, ...
11115, 3, ...

But these would not show any results:
11115, 1, ...
11115, 1, ...
11116, 3, ...
11116, 3, ...
11116, 3, ...
because ID 11115 has no 3 to show and ID 11116 has no 1 to allow showing the 3s

Is it possible to perform such a task?

-- Consumable test data which you should have provided:
CREATE TABLE #t
(
	Col1 int NOT NULL
	,Col2 int NOT NULL
);
INSERT INTO #t
VALUES (11115, 1)
	,(11115, 1)
	,(11115, 3)
	,(11115, 3)
	,(11116, 3)
	,(11116, 3)
	,(11116, 3);

-- Result
SELECT T1.Col1, T1.Col2
FROM #t T1
WHERE T1.Col2 = 3
	AND EXISTS
	(
		SELECT 1
		FROM #t T2
		WHERE T2.Col1 = T1.Col1
			AND T2.Col2 = 1
	);

But I think it might be dynamic? Could be any number not just 3 and 1? Lets what OP says