SQL根据不同的可空列进行筛选

Question

我有一个表，其中有3个可空的外键。比如T1_Id、T2_Id、T3_Id。现在使用left join连接这些外键，并且各自的表有Enabled列，指示是否明显启用了该行。

我想根据这个标志进行过滤，但是有两种不同的情况。

第一个场景:T1_Id不为NULL然后检查T1_Id.Enabled是否为1如果T2_Id不是NULL也检查Enabled标志是否为1然后如果T3_Id不是NULL也检查Enabled标志

第二种情况是T1_Id为null． 在这种情况下，过滤掉上面的内容，但不考虑T1_Id.Enabled。

示例查询:

SELECT [p].[Id], 
FROM [dbo].[SomeTable] AS [p]
LEFT JOIN [dbo].[Table1] AS [t1] ON [t1].[Id] = [p].[T1_Id]
LEFT JOIN [dbo].[Table2] AS [t2] ON [t2].[Id] = [p].[T2_Id]
LEFT JOIN [dbo].[Table3] AS [t3] ON [t3].[Id] = [p].[T3_Id]
WHERE [p].[Id] = SomeId
// HOW?
ORDER BY [p].[Id]

我希望这可以用CASE完成，但我不确定如何。

第一个场景中的示例数据:

+-------------------------+
| Id  T1_Id  T2_Id  T3_Id |
+-------------------------+
| 1   1      1      NULL  |
| 2   2      2      3     |
| 3   3      NULL   NULL  |
| 4   1      NULL   NULL  |
| 5   3      2      1     |
| 6   NULL   1      NULL  |
| 7   NULL   2      3     |
| 8   NULL   3      NULL  |
| 9   NULL   3      1     |
| 10  NULL   2      1     |
+-------------------------+

表1:

+-------------+
| Id  Enabled |
+-------------+
| 1   1       |
| 2   1       |
| 3   1       |
+-------------+

表2:

+-------------+
| Id  Enabled |
+-------------+
| 1   0       |
| 2   1       |
| 3   1       |
+-------------+

表3:

+-------------+
| Id  Enabled |
+-------------+
| 1   0       |
| 2   1       |
| 3   1       |
+-------------+

在这个场景中，查询应该返回Id: 2, 3, 4, 7, 8

Answer 1

SELECT [p].[Id]
FROM [dbo].[SomeTable] AS [p]
LEFT JOIN [dbo].[Table1] AS [t1] ON [t1].[Id] = [p].[T1_Id]
LEFT JOIN [dbo].[Table2] AS [t2] ON [t2].[Id] = [p].[T2_Id]
LEFT JOIN [dbo].[Table3] AS [t3] ON [t3].[Id] = [p].[T3_Id]
WHERE (p.T1_Id IS NOT NULL AND t1.Enabled =1 OR p.T1_Id IS NULL) 
AND (p.T2_Id IS NOT NULL AND t2.Enabled=1 OR p.T2_Id IS NULL)
AND (p.T3_Id IS NOT NULL AND t3.Enabled=1 OR p.T3_Id IS NULL)

Answer 2

如果您只是翻转逻辑，听起来要容易得多:您想知道外键是否有与Enabled = 0

匹配的行

SELECT t.Id
FROM dbo.SomeTable t
WHERE NOT EXISTS (SELECT 1
FROM dbo.Table1 t1
WHERE t1.Id = t.T1_Id
AND t1.Enabled = 0
) AND NOT EXISTS (SELECT 1
FROM dbo.Table2 t2
WHERE t2.Id = t.T2_Id
AND t2.Enabled = 0
) AND NOT EXISTS (SELECT 1
FROM dbo.Table3 t3
WHERE t3.Id = t.T3_Id
AND t3.Enabled = 0
);

,db&lt的在小提琴