Big condtional joins - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Big condtional joins

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-08-09 : 14:25:01

I've got a stored procedure that's currently building a huge dynamic SQL query and then executing it. I'd like to convert it into pure SQL and do away with dynamic SQL. However, the query being built is fairly complex and can join anywhere between 3 and 7 tables.

The trouble I'm having is that when I do what I think of as a conditional join, SQL server is still doing all of the work necessary for the real join. For instance, with a basic mockup of my app:


create table users (user_id int identity,
                    i_zip_codes int)

create table zip_codes (i int identity,
                        zip varchar(25))

CREATE PROCEDURE p_ShowUser (@tiShowZip tinyint) AS
select user_id,CASE @tiShowZip
                 WHEN 1 THEN zip_codes.zip
                 ELSE 0
               END
   from users
   join static..zip_codes on
     (@tiShowZip=0)
     or
     (zip_codes.i=users.i_zip_codes)

...but what seems to be happening there is that, becase @tiShowZip=0 always evaluates to true, every row in zip_codes matches. What I'm trying to do, of course, is only include the result and join from zip_codes if @tiShowZip=1, and skip that whole process when the procedure is called with @tiShowZip=0.

Any thoughts?

Cheers, and thanks
-b

<edit> to fix display problem </edit>

Edited by - robvolk on 08/09/2002 14:27:51

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-09 : 14:37:45

Well, you have a SELECT statement with 2 tables in the FROM clause with a JOIN condition; naturally the optimizer is going to include both tables in its plan. The only way for the query to use the one table is to eliminate the other table from the FROM clause. Use an IF @tiShowZip=0...ELSE... construct to create the proper FROM clauses accordingly.

If you post the entire, actual procedure you have now, and/or a detailed description of what you're searching, we can try to optimize it a little more. IMHO though having between 3 and 7 tables joined is probably better done through dynamic SQL than multiple IF blocks (just for clarity's sake), assuming a redesign of the tables is not possible.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-08-09 : 14:41:21

If you want to keep the same syntatic structure you have there you can left join all of your 'conditional' joins and explicitly specify in the ON clause what you want:

e.g.


select ...
from users u
  left join static..zip_codes z on (z.i = u.i_zip_codes and @tiShowZip = 1)

Jonathan Boott, MCDBA
{0}

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-08-09 : 15:33:13

Thanks for the ideas; it looks like I'm probably stuck with dynmaic SQL for now; I need some of the conditional joins to be inner joins, and the big "if" approach does seem like a mess.

I'd love to post the actual code, but unfortunately I'm not allowed to; it's pretty much the heart of a proprietary application.

Cheers... thanks for the help!
-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources