根据条件加入不同的表 [英] Joining to different tables based on a condition

问题描述

嘿部队，


我不确定这是否允许，但我正在努力保持我的SP通用并减少重复代码。根据条件，我需要JOIN到不同的表，这会稍微改变结果集。所以，我稍后会根据检查相同的条件（@TravelType =''A''）来获取正确的字段。


这是我的样本我正在做，目前在''CASE''附近给我一个语法错误：


CASE WHEN @TravelType =''A''

THEN

LEFT JOIN AirportCity DepCity

ON DepFrom = DepCity.Code


LEFT JOIN AirportCity ArrCity

ON ArrTo = ArrCity.Code

ELSE

LEFT JOIN City DepCity

ON DepartureAirportCode = DepCity.CityCode

AND DepCity.TypeCode =''R''


LEFT JOIN City ArrCity

ON ArrivalAirportCode = ArrCity.CityCode

AND ArrCity.TypeCode =''R''

END


有人可以解释为什么会抛出错误吗？或者，还有另一种方法可以实现吗？


谢谢！

Hey Troops,

I''m not sure if this is allowed, but I''m trying to keep my SP generic and reduce duplicate code. Depending on a condition, I need to JOIN to different tables, which slightly changes the result set. So, I would include conditions later to grab the proper fields based on checking the same condition (@TravelType = ''A'').

Here''s a sample of what I''m doing, which is currently giving me a syntax error near ''CASE'':


CASE WHEN @TravelType = ''A''
THEN
LEFT JOIN AirportCity DepCity
ON DepFrom = DepCity.Code

LEFT JOIN AirportCity ArrCity
ON ArrTo = ArrCity.Code
ELSE
LEFT JOIN City DepCity
ON DepartureAirportCode = DepCity.CityCode
AND DepCity.TypeCode = ''R''

LEFT JOIN City ArrCity
ON ArrivalAirportCode = ArrCity.CityCode
AND ArrCity.TypeCode = ''R''
END

Can someone shed some light on why this throws an error? Or, is there another way to accomplish such?

Thanks!

推荐答案

尝试这样的事情......

Try something like this...

展开 | 选择 | Wrap | 行号

是否将条件置于ON子句中？所以，如果@TravelType =''A''它只会执行第一个JOIN，如果@TravelType =''B''只执行第二次JOIN？

Is that putting the condition within the ON clause? So, it will only do the first JOIN if @TravelType = ''A'' and only do the second JOIN if @TravelType = ''B''?

我看到其他示例如但是，在我们的案例中@TravelType需要是要加入的表中的字段。 @TravelType只是一个局部变量。但如果你说它会起作用，我会试一试。

I see other examples like that, but the @TravelType in our case would need to be a field in the table being joined to. @TravelType is just a local variable. But if you''re saying it''ll work, I''ll give it a shot.

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