SQL conditional nested query

Question

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.

Have you read similar question on SO? Have a look here: stackoverflow.com/a/1850031/1083946. — Alberto De Caro
– Alberto De Caro, Commented Sep 1, 2014 at 9:37

Tomalak · Accepted Answer · 2014-09-01 09:48:44Z

2

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

answered Sep 1, 2014 at 9:48

Tomalak

340k68 gold badges547 silver badges635 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ako Over a year ago

Could you explain why union fits here. Does it work properly if 2 result sets have the same rows?

Tomalak Over a year ago

Union fits here because the WHERE clauses are mutually exclusive. I thought that would be obvious.

Tomalak Over a year ago

The fact that you asked this question in the first place indicates an error in the database design. In a well-designed database you should not have to "switch tables" in a query.

Shaul Behr · Accepted Answer · 2014-09-01 09:42:13Z

0

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

answered Sep 1, 2014 at 9:42

Shaul Behr

38.3k73 gold badges264 silver badges403 bronze badges

Comments

Vikram Singh · Accepted Answer · 2014-09-01 11:34:12Z

0

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)

answered Sep 1, 2014 at 11:34

Vikram Singh

563 bronze badges

Collectives™ on Stack Overflow

SQL conditional nested query

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related