CODEWITHSUNDEEP
oracle-databaseplsql
ikaln00
ikaln00

Reputation: 95

How to conditionally join only some of multiple tables?

I need to find data about a person by ID, and the data could be in either of two tables - either a table of customers or of employees. I have a value showing which table to look for the person in. Also, this search needs to be included in a larger query.

It seems that writing some type of conditional join is what should be done. But how to do that? I've been googling and found nothing. I need something along the lines of

if person_type = 0 then outer left join customers_table
elsif person_type = 1 then outer left join employees_table

Upvotes: 1

Views: 202

Answers (1)

Littlefoot
Littlefoot

Reputation: 143023

Outer join them both, with additional condition included.

Lines #1 - 14 represent sample data; query you need begins at line #15.

SQL> with
  2  person (id, name, person_type) as
  3    (select 1, 'Scott', 0 from dual union all
  4     select 2, 'King' , 1 from dual
  5    ),
  6  customers (id, name) as
  7    (select 1, 'Customer Scott' from dual union all
  8     select 2, 'Customer King'  from dual
  9    ),
 10  employees (id, name) as
 11    (select 1, 'Employee Scott' from dual union all
 12     select 2, 'Employee King'  from dual
 13    )
 14  --
 15  select p.id, p.name, p.person_type,
 16    nvl(c.name, e.name) cust_emp_name
 17  from person p left join customers c on p.id = c.id and p.person_type = 0
 18                left join employees e on p.id = e.id and p.person_type = 1
 19  order by p.id;

        ID NAME  PERSON_TYPE CUST_EMP_NAME
---------- ----- ----------- --------------
         1 Scott           0 Customer Scott
         2 King            1 Employee King

SQL>

Upvotes: 2

Related Questions