Conditional joins

[Chumley40]

Hi all,
I am trying to create sql that only joins to a temp table if the table exists. For example:
If parameter a = 1 the final select would be
select * from Table t
inner join Table1 t1
on t1.field = t.field

If parameter a = 2 the final select would be
select * from Table t
inner join table2 t2
on t.field = t2.field

If parameter a = 1,2 the final select would be
select * from Table t
inner join table1 t1
on t1.field = t.field
inner join table2 t2
on t2.field = t.field


The reasoning for this is because based on the parameter, different fields should be returned (thus the *), but I do not want a bunch of empty fields from a temp table that is empty. Therefore, the table is only created if it is needed. The problem is that there are numerous potential combinations, and I prefer not to do a bunch of if statements with the various combinations.

any help is greatly appreciated!
TJ

 

[ElEye]

Have you tried using COALESCE to iterate thru values?

Dave
[

http://flashspot.com

]

 

[gmmastros]

There are a couple of ways you can accomplish this.

1. You cannot join to a table that does not exist, but you could use IF statements very similar to the logic presented in your question:

If @a = 1 
  Begin
    select * from Table t
    inner join Table1 t1
    on t1.field = t.field
  End
Else If @a = 2 
  Begin
    select * from Table t
    inner join table2 t2
    on t.field = t2.field
  End
Else
  Begin
    select * from Table t
    inner join table1 t1
    on t1.field = t.field
    inner join table2 t2
    on t2.field = t.field
  End

Alternatively, you could use dynamic SQL to execute the final select. Something like this:

Declare @SQL VarChar(8000)

If @a = 1 
  Begin
    Set @SQL = 'select * from Table t
                inner join Table1 t1
                on t1.field = t.field'
  End
Else If @a = 2 
  Begin
    Set @SQL = 'select * from Table t
                inner join table2 t2
                on t.field = t2.field'
  End
Else
  Begin
    Set @SQL = 'select * from Table t
                inner join table1 t1
                on t1.field = t.field
                inner join table2 t2
                on t2.field = t.field'
  End

Exec (@SQL)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gk53]

you can use

 select * from Table t
    left join Table1 t1
    on t1.field = t.field
    left join table2 t2
    on t.field = t2.field
 where 'Y' = case when @a = 1 and t1.field is not null then  'Y' else 'N' end
	and  
	 'Y' = case when @a = 2 and t2.field is not null then  'Y' else 'N' end

it will produce the same columns on output regardless from your parameters

 

[philhege]

I'm answering the original question as I interpret it, which is: you don't know whether or not either temp table exists.
In that light, I'm not sure of the use of the @a parameter. But this should work in any case.

My take

IF OBJECT_ID('tempdb..#temptable1') is not NULL
drop table #temptable1
IF OBJECT_ID('tempdb..#temptable2') is not NULL
drop table #temptable2
--comment either or both of these out to test the different outcomes
select 1 as field into #temptable1
select 1 as field into #temptable2
	 
	 declare @sql varchar(1024)
	 declare @exists int
	 set @exists = 0
	 set @sql = 'SELECT * FROM Table1 t'
	 IF OBJECT_ID('tempdb..#temptable1') is not NULL
	 BEGIN
	  set @sql = @sql + ' JOIN #temptable1 t1 ON t1.field = t.field '
	  set @exists = 1
	 END
	 IF OBJECT_ID('tempdb..#temptable2') is not NULL
	   BEGIN
	   set @sql = @sql + ' JOIN #temptable1 t2 ON t2.field = t.field '
	   
	   set @exists = 1
	   END
	 if @exists = 1 --I assume you don't want the statement to execute if neither table exists
	 BEGIN
	 
	 print @sql
	 --EXEC (@SQL) --uncomment for execution
	 END

-----------
With business clients like mine, you'd be better off herding cats.