By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Joining to different tables based on a condition

P: 6
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!
Feb 18 '10 #1
Share this Question
Share on Google+
12 Replies


ck9663
Expert 2.5K+
P: 2,878
Try something like this...

Expand|Select|Wrap|Line Numbers
  1.  
  2. FROM YourMainTable
  3. LEFT JOIN AirportCity DepCity ON @TravelType = 'A' and DepFrom = DepCity.Code
  4. LEFT JOIN AirportCity DepCity ON @TravelType = 'B' and SomeOtherColumn = SomeOtherColumnFromSomeOtherTable
  5.  
  6.  
Happy Coding!!!

~~ CK
Feb 18 '10 #2

P: 6
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'?
Feb 18 '10 #3

P: 6
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.
Feb 18 '10 #4

Delerna
Expert 100+
P: 1,134
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
Feb 18 '10 #5

P: 6
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.
Feb 18 '10 #6

ck9663
Expert 2.5K+
P: 2,878
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
Feb 18 '10 #7

P: 2
@stretchtack
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.
Jun 22 '10 #8

Delerna
Expert 100+
P: 1,134
Post the code that is giving you the error
Jun 23 '10 #9

P: 2
Here is the code..
CREATE PROCEDURE GetDetails
@input1 AS VarCHAR(50) = '',
@inputGuid AS UniqueIdentifier
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.Similarly 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.
Jun 23 '10 #10

Delerna
Expert 100+
P: 1,134
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
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.    CASE WHEN [MainTable].[Type]='A' THEN
  3.       [TableA].[Name1] ELSE [TableB].[Name1]
  4.    END
  5.    AS 'Name1',
  6.    CASE WHEN [MainTable].[Type]='A' THEN 
  7.       [TableA].[Name2] ELSE [TableB].[Name2]
  8.    END
  9.    AS 'Name2',
  10. FROM MainTable
  11. LEFT JOIN [TableA] ON [MainTable].[Id] = [FinalTable].[Id]
  12. LEFT JOIN [TableB] ON [MainTable].[Id] = [FinalTable].[Id]
  13.  
  14.  
Jun 23 '10 #11

Delerna
Expert 100+
P: 1,134
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
Expand|Select|Wrap|Line Numbers
  1. SELECT  *
  2. FROM theTable a
  3. JOIN theOtherTable b on a.field = 
  4.    CASE WHEN Condition=True THEN
  5.      b.TrueJoinField ELSE b.FalseJoinField
  6.    END
  7.  
Jun 23 '10 #12

ck9663
Expert 2.5K+
P: 2,878
You can include other conditions in your JOIN, not just relational. Try something like this:

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3.  
  4. SELECT
  5. Name =
  6.    case 
  7.       when MainTable.Type = 'A' then [FinalTableA].[Name1] 
  8.       when MainTable.Type = 'B' then [FinalTableB].[Name1]
  9.       else NULL
  10.    end
  11. FROM MainTable
  12.    left join [TableA] [FinalTableA] ON [MainTable].[Type] = 'A' and [MainTable].[Id] = [FinalTableA].[Id]
  13.    left join [TableA] [FinalTableB] ON [MainTable].[Type] = 'B' and [MainTable].[Id] = [FinalTableB].[Id]
  14. WHERE [MainTable].[Id1] = 1
  15.  
  16.  
Happy Coding!!!

~~ CK
Jun 24 '10 #13

Post your reply

Sign in to post your reply or Sign up for a free account.