有条件的内部加入 [英] Conditional Inner Join

问题描述

我希望能够基于表达式的结果对两个表进行内部联接.

I want to be able to inner join two tables based on the result of an expression.

到目前为止我一直在尝试什么:

INNER JOIN CASE WHEN RegT.Type = 1 THEN TimeRegistration ELSE DrivingRegistration AS RReg
ON
RReg.RegistreringsId = R.Id

RegT是我在此联接之前进行的联接:

RegT is a join I made just before this join:

INNER JOIN RegistrationTypes AS RegT ON R.RegistrationTypeId = RegT.Id

此SQL脚本不起作用.

This SQL-script does not work.

总而言之，如果Type为1，则它应在表TimeRegistration上联接，否则应在DrivingRegistration上联接.

So all in all, if the Type is 1, then it should join on the table TimeRegistration else it should join on DrivingRegistration.

解决方案:

在我的select语句中，我执行了以下联接:

In my select statement I performed the following joins:

INNER JOIN  RegistrationTypes AS RegT ON R.RegistrationTypeId = RegT.Id
LEFT OUTER JOIN TimeRegistration AS TReg ON TReg.RegistreringsId = R.Id AND RegT.Type = 1
LEFT OUTER JOIN DrivingRegistration AS DReg ON DReg.RegistreringsId = R.Id AND RegT.Type <>1

然后我编辑where-clause以输出正确的内容，具体取决于RegType，如下所示:

Then I edited my where-clause to output the correct, depending on the RegType, like this:

WHERE (CASE RegT.Type WHEN 1 THEN TReg.RegistreringsId ELSE DReg.RegistreringsId END = R.Id)

推荐答案

尝试使用LEFT JOIN's

Try putting both tables in the query using LEFT JOIN's

LEFT JOIN TimeRegistration TR ON r.rid = TR.Id AND RegT.type =1 
LEFT JOIN DrivingRegistration DR ON r.rid = DR.Id AND RegT.type <>1 

现在，在您的select子句中，使用

Now, in you select clause, use

CASE RegType.Type WHEN 1 THEN TR.SomeField ELSE DR.someField END as SomeField

另一种选择是使用动态SQL

The other option is to use dynamic SQL

这篇关于有条件的内部加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！