CODEWITHSUNDEEP
sqlsql-serverstored-proceduresleft-joincase
Gauty
Gauty

Reputation: 57

LEFT JOIN with case condition under it

I have a stored procedure which takes @Table1.ColumnName (name of the column) as input parameter. This is the query inside the stored procedure:

select data
from Table1               
    case @Table1.ColumnName = 'Table1.column1'  
       THEN LEFT JOIN Table2  (on Table1.column1 = Table2.column1)

   case @Table1.ColumnName = 'Table1.column2' 
       THEN LEFT JOIN Table2 
             (on Table1.column2 = Table2.column1)

I need to make a left join in the stored procedure by checking the input parameter @Table1.ColumnName. I need to alter the ON condition in the join.

I am not sure with the syntax. Could anyone help me how to write a left join with case condition.

Upvotes: 1

Views: 744

Answers (4)

SqlZim
SqlZim

Reputation: 38023

You could use dynamic sql for this:

declare @sql nvarchar(max);

set @sql = '
  select data
  from Table1
    left join Table2 
      on Table2.column1 = ';
if @Table1.ColumnName = 'Table1.column1'
begin;
  set @sql = @sql + 'Table1.column1';
end;
if @Table1.ColumnName = 'Table1.column2'
begin;
  set @sql = @sql + 'Table1.column2';
end;

exec sp_executesql @sql;

reference:

Upvotes: 2

xQbert
xQbert

Reputation: 35323

Can't you just do two left joins since the @table1.column1 variable won't match both, you know you'll only get results in one of them. and you can always coalesce the results so you get the non-empty value from t2, t3 (col1 example below)

SELECT t1.*, coalesce(t1.column1, t2.column1) as Col1
FROM  Table1 t1
LEFT JOIN table2 t2 
  on T1.column1 = t2.column1 
 and @table1.column1 = 'Table1.column1'
LEFT JOIN table3 t3 
  on T1.column2 = t3.column1 
 and @table1.column2 = 'Table1.column2'

Otherwise I believe you'd have to use dynamic SQL to accomplish what you're after.

Or I suppose you could union a blank result with a result...

SELECT data
FROM Table1               
LEFT JOIN table2 
  (on Table1.column1 = Table2.column1)
WHERE @Table1.ColumnName = 'Table1.column1'  

UNION ALL

SELECT data
FROM Table1               
LEFT JOIN Table2 
  (on Table1.column2 = Table2.column1)
WHERE @Table1.ColumnName = 'Table1.column2'

Upvotes: 2

FLICKER
FLICKER

Reputation: 6683

Trying this code, based on the parameter @ColName, you will use different join condition

declare @ColName int = 'FieldNameHere'
select *
from Table1
    left join Table2
        on (Table1.column1 = Table2.column1 and @ColName = 'FieldNameHere')
            or (Table1.column2 = Table2.column2 and @ColName <> 'FieldNameHere')

Upvotes: 0

Jason Pease
Jason Pease

Reputation: 62

You might try something like this.

SELECT data
FROM Table1               
LEFT JOIN Table2 ON
  @Table1.ColumnName = 'Table1.column1'  
  AND Table1.column1 = Table2.column1
LEFT JOIN Table2 ON
  @Table1.ColumnName = 'Table1.column2' 
  AND Table1.column2 = Table2.column1

Upvotes: 0

Related Questions