Select from table 1 unless there is a relationship in 2 other tables

user_1378841 0
Viewed 55

I need to query a name(s) from the Officials table, but exclude that name if the person has the day blocked.

For example, if Sam has blocked 8/21/2021 and 9/11/2021, he should not be selected if the corresponding dates are selected from the Games table. Sam should show up if 9/18/2021 is selected, however. I have 3 tables:

Officials tbl
RefId      Name
---------------------  
1          Jack
2          Sam
3          Jane

Games tbl                           Blocks tbl
GameId    GameDate               BlockId    RefId    BlockDate
-------------------------        ----------------------
1         8/21/2021               1         2       8/21/2021
2         9/11/2021               2         2       9/11/2021
3         9/18/2021               3         3       8/21/2021 

Desired Output
----------------------------------
If Game 1 is selected: Jack
If Game 2 is selected: Jack and Jane 
If Game 3 is selected: Jack, Sam and Jane

The only 2 tables that are related are the Officials table and Blocks table, with the RefId. I need to compare the BlockDate of Blocks table to GameDate of Games table. I have tried some sql language and this below is obviously not correct, but I'm looking for a way to accomplish what I want to do:

@GameDate datetime,

Select c.Id, c.Name 
From Officials c 
Where In c.Id And Blocks.BlockDate <> Games.GameDate)
tsql sql-server sql
1 Answers

You can do it with NOT EXISTS:

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 = ?
);

See the demo.

user_104988280
Related