This problem is known as Relational Division[/url].

SET NOCOUNT ON;

USE tempdb;

GO

DECLARE @T TABLE (

Rate int NOT NULL,

Shift int NOT NULL,

PRIMARY KEY (Rate, Shift)

);

INSERT INTO @T (

Rate,

Shift

)

VALUES

(1, 1),

(1, 2),

(1, 3),

(2, 5),

(3, 1),

(3, 2),

(3, 3),

(8, 4),

(4, 1),

(4, 2),

(5, 1),

(5, 2),

(5, 3),

(5, 4),

(6, 1),

(7, 2),

(8, 2);

DECLARE @Rate int = 1;

WITH C1 AS (

SELECT

Shift

FROM

@T

WHERE

Rate = @Rate

)

SELECT

A.Rate

FROM

@T AS A

INNER JOIN

C1 AS B

ON A.Shift = B.Shift

GROUP BY

A.Rate

HAVING

COUNT(*) = (SELECT COUNT(*) FROM C1);

GO

/*

Rate

1

3

5

*/