我需要从Officials表中查询一个名字,但是如果这个人的日期被封锁,则排除该名字。
例如,如果Sam屏蔽了8/21/2021和9/11/2021,如果从Games表中选择了相应的日期,则不应该选中Sam。然而,如果2021年9月18日被选中,山姆应该会出现。我有3个表:
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
只有两个表是相关的官员表和块表,与RefId。我需要比较块表的BlockDate到游戏表的GameDate。我已经尝试了一些sql语言,下面显然是不正确的,但我正在寻找一种方法来完成我想做的事情:
@GameDate datetime,
Select c.Id, c.Name
From Officials c
Where In c.Id And Blocks.BlockDate <> Games.GameDate)
您可以使用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 = ?
);