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.
| Author |
Topic |
|
CaseyFletcher
Starting Member
1 Post |
 Posted - 2008-05-01 : 14:53:34
|
| Ok I have a complex query that works great, but now I need to join yet another table but the problem is, the value that links it to the rest of the query is a value that is returned from a case statement earlier in the query...For Example...SELECT var1, var2, var3, CASE T1.Number WHEN 0 THEN T2.Result ELSE T3.Result END as var4 FROM TABLE1 T1 LEFT OUTER JOIN TABLE2 T2 on T2.Value=T1.Value LEFT OUTER JOIN TABLE3 T3 on T3.Value=T1.Value2 Above is a generalized, much smaller example of my working Query.Now what I need to do is join TABLE4 but the value I need to connect it to the rest of the query is var4, which can come from 2 different places depending on the value of T1.NumberBasically the logic I need to make it work is to either have a case statement in my JOIN. Like, 'LEFT OUTER JOIN TABLE4 on T4.Value=(CASE T1.Number WHEN 0 THEN T2.Result ELSE T3.Result END)'Is this even possible and if so, how? I need to keep the logic in the main query to preserve performance...Thank you in advance,Casey |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-02 : 06:35:52
|
You could do like this:-SELECT *FROM(YourSelectQueryHere) TmpLEFT OUTER JOIN TABLE4 T4ON T4.Value=Tmp.var4 |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-05-02 : 06:46:18
|
or like this:LEFT OUTER JOIN TABLE4 T4 on T4.Value=CASE WHEN T1.Number = 0 THEN T2.Result ELSE T3.Result END --Lumbago |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-05-02 : 06:47:56
|
| Sorry, I didn't actually read your entire post before replying but what you have in green will work with som slight alterations.--Lumbago |
|
|
|
|
|
|
|
|
Complex Query, many joins...