join tables based on param value

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

Transact-SQL (2000)

join tables based on param value

Author

Topic

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-18 : 05:56:55

hey guys and gals.

i was wondering if its possible to join tables based on parameter value. something like:


declare @tableName varchar(50)
set @tableName = 'MyTable3'

select * 
from MyTable1 t1
     inner join MyTable2 t2 on t1.id = t2.id
     case when @tableName = 'MyTable3' then 
          inner join MyTable3 t3 on t1.id = t3.id
     end

this is for a search.
now based on enterd search conditions the sql statment is created
and tables are joined.
i'd like to rewrite this to an sProc.
but as there can be up to 20 tables with 100k rows in them joins can be very demanding and slow.
the search always return only 4 columns with different number of rows.

any other ideas?? dynamic sql is out of the question because i gain nothing from it...

Go with the flow & have fun! Else fight the flow

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-18 : 06:13:13

case when @tableName = 'MyTable3' then
inner join MyTable3 t3 on t1.id = t3.id
end

You can't do conditional joins without dynamic sql but you can simulate it by this but it will be inefficient and you will have to use a case statement for the result probably.

left outer join MyTable3 t3
on @tableName = 'MyTable3'
and t1.id = t3.id
where (@tableName = 'MyTable3' or t3.id is not null)

another option is a union maybe but would need to know more about the query.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-18 : 06:21:21

yeah i thought so....

well the query itself is nothing special:


SELECT 	t1.org_id, t3.plan_year, t4.device_id, t1.Name  
FROM 	-- constant tables
	MyTable1 t1
	LEFT JOIN (MyTable2 t2 
		INNER JOIN MyTable4 t4 ON (t2.device_id = t4.device_id AND t4.main_device=1)
	) ON t1.org_id = t2.org_id 
	LEFT JOIN MyTable3 t3 ON (t1.org_id = t3.org_id AND t3.plan_year = '2004')  
	-- optional tables up to 15 other inner joins
	INNER JOIN 1 ...
	INNER JOIN 2 ...
	...
where	conditions ...

i guess i'll have to do this in another way...

optional tables are dynamically added as well as where conditions based on these tables.
if it matters all of the columns that are joined are uniqueidentifiers...

Go with the flow & have fun! Else fight the flow

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-18 : 07:03:51

does this work..


select * 
from MyTable1 t1
     inner join MyTable2 t2 on t1.id = t2.id
     left outer join MyTable3 t3 on @var = 'MyTable3' and t1.id = t3.id
where (t3.id_column_name is not null or isnull(@var,'') = '')

Ex :


create table a (col1 int)
create table b (col1 int)

insert into a select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8

insert into b select 1
union all select 2
union all select 3
union all select 4

declare @var varchaR(30)
SELECT @var = null --'b'

select a.* from a
left outer join b on @var = 'b' and a.col1 = b.col1
where (b.col1 is not null or isnull(@var,'') = '' )

Hemanth Gorijala
BI Architect / DBA...(yuhoo!!! and now, an "Yak Master")

Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-18 : 07:26:12

well yeah that works

but this is still a join so the tables will be joined, but the results won't show.

all of the tables have 100.000+ rows. so when joining all 20 of them it can be reaaaaallyyyy slow....
i was just wondering if it is possible to omit the table being joined at all based on the parameter value.

basically if i say @tableName = null the table with that name won't be joined at all.

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-18 : 08:44:23

Maybe I'm just missing something... but why is dynamic sql not an option?

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-18 : 09:07:04

well if i'm not mistaken dynamic sql does not use cached plan in sproc so i gain nothing by it...

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-18 : 09:08:30

do not dynamically join. do as many left outer joins as you need based on the conditions, and then use a CASE to take the value from the outer table as required.

I have not seen a situation yet where an OR clause in a join is a good idea or is necessary. if you have "conditional" joins, then do the join different times.

If you wish to save the cost of the join when data from the outer table is not needed, you should probably use an IF in your stored proc and write two separate SQL statements.

- Jeff

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-18 : 09:25:49

could you filter each table for the params associated with that table, store the filter results in a temp table, and then do your join so that the results are prefiltered, and the final query would combine filters from multiple tables? I know I probably didn't say that well...

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-18 : 10:00:56

jeff: yeah i was thinking about that and multi sql statements was my first option,
but i wanted to check with you guys for ideas.

corey: i know what you mean... but as all the tables are joined to the main one via t1.org_id = t(n).org_id
that would create quite an overhead.
i did that with the tables i could using a function... acctually speeded thing up

well thanx for the input all...
if anyone else has any bright ideas you're welcome.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources