CODEWITHSUNDEEP
sqloracle-databasejoincase
KevinT
KevinT

Reputation: 65

Conditionally joining from multiple tables

Does SQL allow some form of conditional "table choosing" within the Join statement? ie. selecting a different table to join based on a predefined variable/condition.

define var = 1
select * 
from tbl
  join (case when &var=1 then tblA when &var=2 then tblB else tblC end) a on tbl.id = a.id

The error I get when attempting this method is ORA-00905: missing keyword.

Upvotes: 3

Views: 45

Answers (2)

Dmitri M
Dmitri M

Reputation: 1156

You still need to specify all joins ahead of time and they would have to be left outer joins, but you can rewrite your statement like this. This way will work regardless of the number of fields in each of the tables (requirement for the union), and if they are named differently then you can access the appropriate field by name.

DECLARE @var int
SET @var=1

select tbl.*, tblA.ShippingName, tblB.BillingName, tblC.OtherName 
from tbl
  left outer join tblA on tbl.id = tblA.id and @var = 1
  left outer join tblB on tbl.id = tblB.id and @var = 2
  left outer join tblC on tbl.id = tblC.id and @var = 3

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

No. Neither SQL nor Oracle allow this, unless you use dynamic SQL.

Assuming the tables have the same columns, you could write this logic as:

select * 
from tbl join
     (select a.* from tblA where &var = 1 union all
      select b.* from tblB where &var = 2 union all
      select c.* from tblC where &var not in (1, 2)
     ) abc
     on tbl.id = abc.id;

Upvotes: 6

Related Questions