Case Statement in Join condition

Question

Case Statement in Join condition

Priya004

Ten Centuries

Points: 1368
More actions
September 2, 2014 at 12:54 am

#292819

IS there any way I can use the below
Select X.* From TABLE X
INNER JOIN
(case when (condition) then TABLE A else TABLE B end) AS Y
ON X.Col1 = Y.Col1

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

Indu-649576 (9/2/2014)
IS there any way I can use the below
Select X.* From TABLE X
INNER JOIN
(case when (condition) then TABLE A else TABLE B end) AS Y
ON X.Col1 = Y.Col1

you will get syntax error if you try to execute this in simple tsql. However, Yes you can do it but in dynamic tsql something like this.

Declare @Query as nvarchar(max) = 'Select X.* From TABLE X INNER JOIN '

+ (case when (condition) then 'TABLE A' else 'TABLE B' end

+ ' AS Y ON X.Col1 = Y.Col1[/quote]'

EXEC SP_EXECUTESQL @Query

Priya004 Ten Centuries Points: 1368 More actions · Answer 2

twin.devil (9/2/2014)
Indu-649576 (9/2/2014)
IS there any way I can use the below
Select X.* From TABLE X
INNER JOIN
(case when (condition) then TABLE A else TABLE B end) AS Y
ON X.Col1 = Y.Col1
you will get syntax error if you try to execute this in simple tsql. However, Yes you can do it but in dynamic tsql something like this.
Declare @Query as nvarchar(max) = 'Select X.* From TABLE X INNER JOIN '
+ (case when (condition) then 'TABLE A' else 'TABLE B' end
+ ' AS Y ON X.Col1 = Y.Col1

'

EXEC SP_EXECUTESQL @Query

[/code][/quote]

I don't think dynamic also works...Can u try with some simple tables in ur db using dynamic

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Indu-649576 (9/2/2014)
IS there any way I can use the below
Select X.* From TABLE X
INNER JOIN
(case when (condition) then TABLE A else TABLE B end) AS Y
ON X.Col1 = Y.Col1

Not literally, no - but there are numerous ways of obtaining the same logical result. Can you show the condition, as well as the joins between x and a, and x and y?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Umer Akram SSC-Insane Points: 22209 More actions · Answer 4

Indu-649576 (9/2/2014)
twin.devil (9/2/2014)
Indu-649576 (9/2/2014)
IS there any way I can use the below
Select X.* From TABLE X
INNER JOIN
(case when (condition) then TABLE A else TABLE B end) AS Y
ON X.Col1 = Y.Col1
you will get syntax error if you try to execute this in simple tsql. However, Yes you can do it but in dynamic tsql something like this.
Declare @Query as nvarchar(max) = 'Select X.* From TABLE X INNER JOIN '
+ (case when (condition) then 'TABLE A' else 'TABLE B' end
+ ' AS Y ON X.Col1 = Y.Col1
'
EXEC SP_EXECUTESQL @Query
[/code]

I don't think dynamic also works...Can u try with some simple tables in ur db using dynamic[/quote]

I assumed your scenario like this, on a given condition choose either TableA or TableB (Both tables contain Col1). If this is true dynamic sql does work. Following is the example to help you understand this.

USE tempdb

GO

Create table TableA (Col1 Int)

Create table TableB (Col1 Int)

Create table TableX (Col1 Int)

Declare @Query AS NVARCHAR(MAX)

Insert into TableX

Select 1 union all

select 3 union all

select 5

Insert into TableA

Select 1 union all

select 2 union all

select 3

Insert into TableB

Select 4 union all

select 5 union all

select 6

------------- For TableA

Select @Query = 'Select X.* From TableX AS X Inner join ' + (Case WHEN 1 = 1 THEN 'TableA' ELSE 'TableB' END) + ' AS Y ON Y.Col1 = X.Col1'

Print (@Query)

Exec sp_executesql @Query

------------- For TableB

Select @Query = 'Select X.* From TableX AS X Inner join ' + (Case WHEN 1 = 0 THEN 'TableA' ELSE 'TableB' END) + ' AS Y ON Y.Col1 = X.Col1'

Print (@Query)

Exec sp_executesql @Query

Drop table TableA

Drop table TableB

Drop table TableX

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Twin-devil: I think the OP wants to apply a row-level condition using values from table X, in which case dynamic sql won't work.

The most common choice would be left joins to the two "conditional" tables with the two mutually-exclusive filter conditions applied in the left joins.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Umer Akram SSC-Insane Points: 22209 More actions · Answer 6

ChrisM@Work (9/2/2014)
Twin-devil: I think the OP wants to apply a row-level condition using values from table X, in which case dynamic sql won't work.
The most common choice would be left joins to the two "conditional" tables with the two mutually-exclusive filter conditions applied in the left joins.

you might also be right about this Chris, actually OP didn't provide enough information and i just totally guessed that there might be a case when you need to Choose between 2 different tables but joining condition remains the same.

so its depends on OP what actually he/she is looking for.:-)

Priya004 Ten Centuries Points: 1368 More actions · Answer 7

Thanks twin devil..this what im looking for..it helped me

One doubt is that how will a case stmt work in dynamic sql without being in quotes

Umer Akram SSC-Insane Points: 22209 More actions · Answer 8

Priya004 (9/9/2014)
Thanks twin devil..this what im looking for..it helped me
One doubt is that how will a case stmt work in dynamic sql without being in quotes

Let me clear you on this so that you should not have any more doubts about it.

Following query will not work, because table definition can not be decided on run time, its have to be decided on design time.

Select

from Table1

join Case When 1= 0 then Table2 Else Table3 END

So how to solve this problem. you got two Options

1) Chris suggested i.e "The most common choice would be left joins to the two "conditional" tables with the two mutually-exclusive filter conditions applied in the left joins."

2) Dynamic SQL solution shared by me earlier. because its dynamic you can choice the table definition at run time.

There are always N number for ways to achieve solution in sql. you just have to choose which one suits you well.

Priya004 Ten Centuries Points: 1368 More actions · Answer 9

Hey..i got the reason after posting the query..Im clear now

Anyways Thanks a lot!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Priya004 (9/10/2014)
Hey..i got the reason after posting the query..Im clear now
Anyways Thanks a lot!

Can you post your chosen solution please?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden