CODEWITHSUNDEEP
sqloracle10g
Victor
Victor

Reputation: 17107

sql conditional join

I have 3 tables: 

table1:table1_col1, table1_col2,table1_col3
table2 : table1_col1, table2_col2,table2_col3
table3 : table1_col1, table3_col2,table3_col3

As I tried to explain by the naming, table1:table1_col1 is a unique key that can reference either table2 : table1_col1 or table3 : table1_col1 but never both. I need to make a join between table1, table2,table3 such that:

join table1 with table2 if table1:table1_col1 = table2 : table1_col1
join table1 with table3 if table1:table1_col1  = table3 : table1_col1

Is this possible in sql syntax?
Thank you.

Upvotes: 1

Views: 379

Answers (2)

DRapp
DRapp

Reputation: 48179

I would assume you want the corresponding columns from table's 2 and 3 too.

select 
      t1.table_col1,
      t2.table2_col2,
      t2.table2_col3,
      t3.table3_col2,
      t3.table3_col3
   from
      table1 t1
         left join table2 t2
           on t1.table1_col1 = t2.table1_col1
         left join table3 t3
           on t1.table1_col1 = t3.table1_col1

Additionally, if you only wanted the columns from respective table 2 or 3, and they were the same data types, you could use the NVL() function, such as 

select 
      t1.table_col1,
      nvl( t2.table2_col2, t3.table3_col2 ) as SecondCol,
      nvl( t2.table2_col3, t3.table3_col3 ) as ThirdCol
   from
      table1 t1
         left join table2 t2
           on t1.table1_col1 = t2.table1_col1
         left join table3 t3
           on t1.table1_col1 = t3.table1_col1

Upvotes: 1

The Scrum Meister
The Scrum Meister

Reputation: 30141

You can use a LFET JOIN:

FROM table1 
LEFT JOIN table2 ON table1.table1_col1 = table2.table1_col1
LEFT JOIN table3 ON table1.table1_col1  = table3.table1_col1

Upvotes: 1

Related Questions