SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamically choosing inner or outer join


Dynamically choosing inner or outer join

Author
Message
jcb
jcb
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3376 Visits: 997
Hi,

Today a developer bring me a problem.
He need to rewrite a SP and add some parameters to filter the results.

The SP is like:

[SP_OLD] @param1
as
select tableA ta
left join tableB tb on tb.idA=ta.idA
left join tableC tc on tc.idB=tb.idB
where
ta.param1 = @param1

and now needs to evaluate like:

[SP_NEW] @param1, @param2
as

set @innerjoin = dosomethingwith(param1,param2)

if @innerjoin = false

select *
from tableA ta
left join tableB tb on tb.idA=ta.idA
left join tableC tc on tc.idB=tb.idB
where
ta.param1 = @param1
and ta.param2 = @param2

else

select *
from tableA ta
inner join tableB tb on tb.idA=ta.idA
inner join tableC tc on tc.idB=tb.idB
where
ta.param1 = @param1

I guess the better way do do it is create TWO SPs and let the app chose one or another...but the developer don't want to change some VB6 dlls (he got enough dll hell from this project).

I managed to do it using IF..ELSE but i wonder if exists a better way. Can i use CASE in the JOIN clause?

The query is pretty big and uses old fashioned joins in the WHERE clause (=, =* and *=).

I will use the better answer to rewrite lots of scripts in a old-big-bad BD designed app.

Thanks in advance.
Jean C. Bulinckx
Vladan
Vladan
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5456 Visits: 761
You can't use CASE to determine whether the join will be LEFT JOIN or INNER JOIN if that's what you were asking.
You probably could use additional conditions in WHERE clause to achieve similar effect, but IMHO your solution with IF and two separate blocks of code is better. See the code below for how that could be done (the idea is tested and works, but you may need to modify it for your environment - you didn't explain much about the @innerjoin parameter, so I wrote it as if it was varchar with values "Yes" or "No").


SELECT *
FROM tableA ta
LEFT JOIN tableB tb ON tb.idA=ta.idA
LEFT JOIN tableC tc ON tc.idB=tb.idB
WHERE ta.param1 = @param1
AND ta.param2 = @param2
AND (tb.idA=ta.idA OR @innerjoin = 'No')
AND (tc.idB=tb.idB OR @innerjoin = 'No')



Edit: To be correct, the way I wrote it, any other value for @innerjoin than 'No' will cause that the query will perform as inner joined.



GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228663 Visits: 46343
jcb (6/16/2008)


I guess the better way do do it is create TWO SPs and let the app chose one or another...but the developer don't want to change some VB6 dlls (he got enough dll hell from this project).


Write 3 stored procs. The app always calls the one and passed the param to say left or inner join. Based on that the proc calls one of 2 other procedures. No change to the dll needed.

I must admit, I'm curious about what would need the join type changing at run time. Reports?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Michael Earl-395764
Michael Earl-395764
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13479 Visits: 23078
You could always do a LEFT JOIN and just specify a filter based on your @innerjoin parameter

select *
from tableA ta
left join tableB tb on tb.idA=ta.idA
left join tableC tc on tc.idB=tb.idB
where
ta.param1 = @param1
and (@innerjoin = false or ta.param2 = @param2)
AND (@innerjoin = false or tb.idA is not null)
AND (@innerjoin = false or tc.idB is not null)

jcb
jcb
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3376 Visits: 997
Nice work guys!

Vladan,

yep, I wondered about a case in the where clause. Why not?




GilaMonster,

Great! I can keep the original SP and make 2 news SPs in that context.
It´s pretty good because the app is a monster and is hard to determine if are others calls to that SP over thousands of SPs, triggers, ASP, DLLs, jobs...

PS: This call is used to validate something in a long and complex chain of rules (from my view point its like one of Tom's trap to catch Jerry).



Michael Earl,

Nice and direct like the Vladan solution.
That was my first approach. Unfortunelly the real world query uses joins in the where clause and i got a run time error.
Just because u cannot join the same tables "twice" while the same tables are in a outer join. I tested u query and it worked. Maybe its a limitation only for "old fashioned" joins.



Thanks for all u time guys!
Vladan
Vladan
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5456 Visits: 761
jcb,
this is one of my favorite links - it discusses how to apply various search criteria, and mentions many possible solutions, their advantages, drawbacks and performance. Maybe it will help you to choose the best way.
-> Dynamic Search Conditions in T-SQL

Many other interesting articles by the same author can be accessed from Erland Sommarskog's home page



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search