CASE for two diifferent join conditions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CASE for two diifferent join conditions

Author

Topic

learntsql

524 Posts

Posted - 2009-12-29 : 05:31:23

Hi...
I pass parameter for Storedprocedure
Based on the parameter value it should map two different tables.
eg:
I have 5 tables like T1,T2,T3,T4 and T5
WHEN Parameter Value=Value1
Then it should map T1,T2,<T4>,T5
ELSE
it should map T1,T2,<T3>,T5

Can i do it in single select Statement?
If so How?
Or any other ways?
Should i follow IF ELSE logic?
TIA.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-29 : 05:32:52

Use Dynamic SQL!!!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-29 : 05:38:09

Try and avoid dynamic sql - for so many reasons.

It's not clear from the post whether you meant that you want to bring back the value from a different table / column depending on the paramater or if you wanted to do a whole different query.

Post the queries you would use if you were to do them as 2 seperate queries. My gut feeling is that you will probably be able to get round the problem without using any dynamic sql and maybe just using CASE to bring back the relevent table / column

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

learntsql

524 Posts

Posted - 2009-12-29 : 05:50:52

sorry if i am not clear
let me explain
In Select Clause i need to display few columns but one column is changed based on joins as i mentioned above.
eg:

when parameter value is Value1 then This column must be pick from <T4> table
ELSE
<T3> table
under same column.
as like

WHEN Parameter Value=Value1
Then it should map T1,T2,<T4>,T5
ELSE
it should map T1,T2,<T3>,T5

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-29 : 06:19:53

I understand what you are asking for. Can you post the queries you would use to get the information manually?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

raky
Aged Yak Warrior

767 Posts

Posted - 2009-12-29 : 08:05:28

quote:
Originally posted by learntsql

sorry if i am not clear
let me explain
In Select Clause i need to display few columns but one column is changed based on joins as i mentioned above.
eg:

when parameter value is Value1 then This column must be pick from <T4> table
ELSE
<T3> table
under same column.
as like

WHEN Parameter Value=Value1
Then it should map T1,T2,<T4>,T5
ELSE
it should map T1,T2,<T3>,T5

some thing like this

SELECT T1.COL1,T2.COL2,CASE WHEN @Param = 'Value1' THEN T4.COL4 ELSE T3.COL3 END AS COL3,T5.COL5 AS COL4
FROM Table1 T1
LEFT JOIN Table2 T2 <Some join condition>
LEFT JOIN Table3 T3 <Some join condition>
LEFT JOIN Table4 T4 <Some join condition>
LEFT JOIN Table5 T5 <Some join condition>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources