0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and amateur woodworkers

CREATE TABLE #Tbl(Id INT, Letter CHAR(1), Number INT);
INSERT INTO #Tbl VALUES
(23, 'A', 1), (23, 'A', 2), (23, 'B', 1), (23, 'B', 2), (81, 'A', 1), (81, 'B', 2);

DECLARE @Letters TABLE(Letter CHAR(1));
DECLARE @Numbers TABLE(Number INT);

INSERT INTO @Letters VALUES ('A'), ('B');
INSERT INTO @Numbers VALUES (1), (2);
WITH CteCross(Letter, Number) AS(
	SELECT Letter, Number 
	FROM @Letters
	CROSS JOIN @Numbers
)
SELECT t.Id, t.Letter
FROM #Tbl t
INNER JOIN CteCross cc
	ON cc.Letter = t.Letter
	AND cc.Number = t.Number
GROUP BY t.Id, t.Letter
HAVING COUNT(*) = (SELECT COUNT(*) FROM CteCross);

DROP TABLE #Tbl;

fork query

edited jan 21

created dec 12 16 anonymous