具有两个函数的条件内部联接

Question

我有两个函数。我需要根据BIT值来决定是否加入这两个。

ON APEL.data_Period_dataination_Lookup_ID = CCEL.data_Period_dataination_Lookup_ID
INNER JOIN  markcommon.GetPredecessordatadataIds() AS PIDS
IF @Combined_Flag=1 
BEGIN
ON PIDS.data_Period_dataination_Identifier = APEL.data_Period_dataination_Identifier
AND PIDS.data_Period_Identifier = APL.data_Period_Identifier
END

基本上

如果BIT=0加入功能1，则加入功能2

我试着加入IF条款。。但它似乎不起作用。做这件事的正确方法是什么？

Answer 1

只需将静态条件添加为联接条件的一部分，并使用LEFT JOIN来确保它能处理缺失的行。然后，您可以在选择中使用case表达式来获得正确的列，例如

SELECT
CASE WHEN F1.id IS NOT NULL THEN F1.MyColumn ELSE F2.MyColumn END
FROM ...
LEFT JOIN markcommon.Function1() AS F1
ON @Combined_Cohort = 1
AND {The rest of the join conditions}
LEFT JOIN markcommon.Function2() AS F2
ON @Combined_Cohort = 0
AND {The rest of the join conditions}

Answer 2

我认为您可以使用LEFT JOIN:

ON APEL.data_Period_dataination_Lookup_ID = CCEL.data_Period_dataination_Lookup_ID LEFT JOIN
markcommon.GetPredecessordatadataIds() PIDS
ON @Combined_Cohort = 1 AND
PIDS.data_Period_dataination_Identifier = APEL.data_Period_dataination_Identifier AND
PIDS.data_Period_Identifier = APL.data_Period_Identifier
. . .
WHERE @Combined_Cohort <> 1 OR PIDS.data_Period_dataination_Identifier IS NOT NULL

Answer 3

您可以按如下方式使用LEFT JOIN：

APEL.data_Period_dataination_Lookup_ID = CCEL.data_Period_dataination_Lookup_ID
LEFT JOIN  markcommon.GetPredecessordatadataIds() AS PIDS
ON (@Combined_Flag=1 AND PIDS.data_Period_dataination_Identifier = APEL.data_Period_dataination_Identifier
AND PIDS.data_Period_Identifier = APL.data_Period_Identifier
)
LEFT JOIN FUNCTION2() AS PIDS2
ON (@Combined_Flag=0 AND <<join condition for function 2>>
)

Answer 4

还请检查：

SELECT
IIF(F1.id IS NOT NULL,F1.MyColumn,F2.MyColumn)    
FROM ...
LEFT JOIN markcommon.Function1() AS F1
ON @Combined_Cohort = 1 
AND {The rest of the join conditions}
LEFT JOIN markcommon.Function2() AS F2
ON @Combined_Cohort != 1 
AND {The rest of the join conditions}