SQL filter out based on different nullable columns-CodePudding

I have a table where there is 3 nullable foreign keys. Lets say T1_Id, T2_Id, T3_Id. Now those foreign keys are joined using left join and the respective tables have Enabled column indicating if the row is enabled obviously.

I want to filter out based on this flag, but there is 2 different scenarios.

First scenarion: T1_Id will not be NULL so then check if T1_Id.Enabled is 1 and then if T2_Id is not NULL also check if Enabled flag is 1 and then if T3_Id is not NULL also check the Enabled flag.

Second scenario is where T1_Id will be null. In that case filter out as above but without taking T1_Id.Enabled as consideration.

Example query:

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]

I expect this can be done with CASE but I'm not sure how.

Example data in first scenario:

 ------------------------- 
| 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     |
 -------------------------

Table 1:

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

Table 2:

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

Table 3:

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

In this scenario the query should return Id: 2, 3, 4, 7, 8

CodePudding user response：

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)

CodePudding user response：

It sounds like it's much easier if you just flip the logic: you want to know if the foreign keys don't have a matching row with 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<>fiddle