Joining to different tables based on a condition

[stretchtack]

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 DepartureAirpor tCode = DepCity.CityCod e
AND DepCity.TypeCod e = 'R'

LEFT JOIN City ArrCity
ON ArrivalAirportC ode = ArrCity.CityCod e
AND ArrCity.TypeCod e = 'R'
END

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

Thanks!

[ck9663]

Try something like this...

FROM YourMainTable
LEFT JOIN AirportCity DepCity ON @TravelType = 'A' and DepFrom = DepCity.Code
LEFT JOIN AirportCity DepCity ON @TravelType = 'B' and SomeOtherColumn = SomeOtherColumnFromSomeOtherTable

Happy Coding!!!

~~ CK

[stretchtack]

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

[stretchtack]

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.

[Delerna]

It actually does both joins but only one of the joins will return any records because @TravelType will be either 'A' or it will be 'B'

Nice approach CK
you may need a case in your select clause so it will select the appropriate field based on the value of @TravelType

[stretchtack]

Ok, so you can put a condition in the 'ON' clause... not just the columns to link on? Slick. We actually found another way to get what we need, but I did learn something. Thanks much for your time fellas.

[ck9663]

Yes you can.

You can even do an always true condition like ON 1 = 1 to pair each record on your main table to every single record on your joined table. This is specially helpful if you need a sequence of some sort for every record in the main table.

So make sure about your conditions...

Happy Coding!!!

~~ CK

[HKNew]

Ok, so you can put a condition in the 'ON' clause... not just the columns to link on? Slick. We actually found another way to get what we need, but I did learn something. Thanks much for your time fellas.

I have also got a similar issue. Need to have a conditional JOIN. I tried with CASE statements but I got syntax error near CASE.
Eg:
IF @Type=A, then I need to JOIN with tableA and get TableA.Name
IF @Type=B, then I need to JOIN with tableB and get TableB.Name
IF @Type=C, then I need to JOIN with tableC and get TableC.Name

Can someone help me out in this.

[Delerna]

Post the code that is giving you the error

[HKNew]

Here is the code..
CREATE PROCEDURE GetDetails
@input1 AS VarCHAR(50) = '',
@inputGuid AS UniqueIdentifie r
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY

SELECT

[FinalTable].[Name1] AS 'Name1',
[FinalTable].[Name2] AS 'Name2',

FROM MainTable

CASE [MainTable].[Type]
WHEN 'A' THEN
INNER JOIN [TableA] [FinalTable] ON [MainTable].[Id] = [FinalTable].[Id]
WHEN 'B' THEN
INNER JOIN [TableB] [FinalTable] ON [MainTable].[Id] = [FinalTable].[Id]
END
WHERE [MainTable].[Id1] = 1

END TRY
BEGIN CATCH
--Rethrow the error.
END CATCH

SET NOCOUNT OFF
END

Based on the type (A or B), the JOIN has to be formed. If the type is 'A' then JOIN should be only with TableA and not with TableB.Similarl y if type is 'B' then should be only with TableB.

The columns, "Name1" land "Name2" should be selected from either of these two tables based on the type.

[Delerna]

Can you explain what [FinalTable] is.

I am asssuming that you mean it as an alias

If that is the case then
this seems more appropriate but I am only guessing at what you are trying to do
[code=sql]
SELECT
CASE WHEN [MainTable].[Type]='A' THEN
[TableA].[Name1] ELSE [TableB].[Name1]
END
AS 'Name1',
CASE WHEN [MainTable].[Type]='A' THEN
[TableA].[Name2] ELSE [TableB].[Name2]
END
AS 'Name2',
FROM MainTable
LEFT JOIN [TableA] ON [MainTable].[Id] = [FinalTable].[Id]
LEFT JOIN [TableB] ON [MainTable].[Id] = [FinalTable].[Id]

[/code]

[Delerna]

Incidentally you cannot do conditional joins like that.
You cannot include the table to join in the true/false parts of the case.
The two tables to join must be outside the case altogether and only the joining fields can be inside.
I hope that makes sense

SELECT  *
FROM theTable a
JOIN theOtherTable b on a.field = 
   CASE WHEN Condition=True THEN
     b.TrueJoinField ELSE b.FalseJoinField
   END

[ck9663]

You can include other conditions in your JOIN, not just relational. Try something like this:

SELECT
Name =
   case 
      when MainTable.Type = 'A' then [FinalTableA].[Name1] 
      when MainTable.Type = 'B' then [FinalTableB].[Name1]
      else NULL
   end
FROM MainTable
   left join [TableA] [FinalTableA] ON [MainTable].[Type] = 'A' and [MainTable].[Id] = [FinalTableA].[Id]
   left join [TableA] [FinalTableB] ON [MainTable].[Type] = 'B' and [MainTable].[Id] = [FinalTableB].[Id]
WHERE [MainTable].[Id1] = 1

Happy Coding!!!

~~ CK