CODEWITHSUNDEEP
sqlsql-serveroracle-database
ako
ako

Reputation: 420

SQL conditional nested query

I have query like:

select * 
from BASE_TABLE
inner join (
    select * from TABLE_A
) t on BASE_TABLE.ID = t.ID

and want to make nested query conditional, something like:

select * 
from BASE_TABLE
inner join (
    if @var = 1 
        select * from TABLE_A
    else 
        select * from TABLE_B
) t on BASE_TABLE.ID = t.ID

Is it possible? Query is used inside stored procedure. Solution should work for SQL Server and Oracle or at least for SQL Server.

Upvotes: 1

Views: 1684

Answers (3)

Vikram Singh
Vikram Singh

Reputation: 56

please try below query

declare @var int=1  
declare @qry nvarchar(max)  
set @qry='select * from BASE_TABLE ('  
if @var=1  
set @qry=@qry+ 'select * from TABLE_A'  
else  
set @qry=@qry+ 'select * from TABLE_B'  

set @qry=@qry+')t on BASE_TABLE.ID = t.ID'

exec (@qry)

Upvotes: 0

Tomalak
Tomalak

Reputation: 338128

How about this:

SELECT
  * 
FROM
  BASE_TABLE
  INNER JOIN (
    SELECT col1, col2, colN FROM TABLE_A WHERE @var IN (1) -- "if"
    UNION
    SELECT col1, col2, colN FROM TABLE_B WHERE @var IN (2) -- "else if"
    UNION
    SELECT col1, col2, colN FROM TABLE_C WHERE @var NOT IN (1, 2) -- "else"
  ) t ON t.ID = BASE_TABLE.ID

Upvotes: 2

Shaul Behr
Shaul Behr

Reputation: 37993

I would do it like this (but then you need to specify fields; you can't use select * syntax):

select base.ID, base.Field1, base.Etc, Field1 = case when @var=1 then a.Field1 else b.Field1 end,
       Field2 = case when @var=1 then a.Field2 else b.Field2 end
  from BASE_TABLE base
  left join TABLE_A a on base.ID = a.ID
  left join TABLE_B b on base.ID = b.ID

Upvotes: 0

Related Questions