By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Officials ([RefId] int, [Name] varchar(4));
INSERT INTO Officials ([RefId], [Name]) VALUES
(1, 'Jack'),
(2, 'Sam'),
(3, 'Jane');
CREATE TABLE Games ([GameId] int, [GameDate] datetime);
INSERT INTO Games ([GameId], [GameDate]) VALUES
(1, '2021-08-21 00:00:00'),
(2, '2021-09-11 00:00:00'),
(3, '2021-09-18 00:00:00');
CREATE TABLE Blocks ([BlockId] int, [RefId] int, [BlockDate] datetime);
INSERT INTO Blocks ([BlockId], [RefId], [BlockDate]) VALUES
(1, 2, '2021-08-21 00:00:00'),
(2, 2, '2021-09-11 00:00:00'),
(3, 3, '2021-08-21 00:00:00');
9 rows affected
SELECT o.*
FROM Officials o
WHERE NOT EXISTS (
SELECT 1
FROM Blocks b INNER JOIN Games g
ON g.GameDate = b.BlockDate
WHERE b.RefId = o.RefId AND g.GameId = 1
)
| RefId | Name |
|---|---|
| 1 | Jack |
SELECT o.*
FROM Officials o
WHERE NOT EXISTS (
SELECT 1
FROM Blocks b INNER JOIN Games g
ON g.GameDate = b.BlockDate
WHERE b.RefId = o.RefId AND g.GameId = 2
)
| RefId | Name |
|---|---|
| 1 | Jack |
| 3 | Jane |
SELECT o.*
FROM Officials o
WHERE NOT EXISTS (
SELECT 1
FROM Blocks b INNER JOIN Games g
ON g.GameDate = b.BlockDate
WHERE b.RefId = o.RefId AND g.GameId = 3
)
| RefId | Name |
|---|---|
| 1 | Jack |
| 2 | Sam |
| 3 | Jane |