0

I have three tables as follows:

table 1          table 2          table 3
-------          -------          -------
a                a                a
b                c                c
c                f                
d
e
f

I want to join this three tables into 1 which will result to the following:

result table
------------

a               a                a
b
c               c                c
d
e
f               f

Noticed that the second and third col contains blank row if it does not have a match. How can I achieve this using oracle sql?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
epsac
  • 198
  • 5
  • 17
  • This is a homework question. Please tell what you've tried so far. – Tomalak Nov 17 '11 at 05:38
  • @Tomalak I tried using select * from table1, table2, table3 where table1.primarykey = table2.primarykey and table2.primarykey = table3.primarykey – epsac Nov 17 '11 at 05:41
  • @OMG Ponies I use the (+) but doesn't achive what I desired. – epsac Nov 17 '11 at 05:42
  • 1
    That's extremely old syntax - there are [questions to explain how to convert it](http://stackoverflow.com/questions/2425960/oracle-old-joins-a-tool-script-for-conversion) – OMG Ponies Nov 17 '11 at 05:43
  • @OM Ponies, my problem with the on statement is it will only join table given a primary key. I'm working on an ETL type of system that does not have primary key. It needs four or more columns in order for me to join tables – epsac Nov 17 '11 at 05:50
  • You don't help yourself by abstracting that in your question details. – OMG Ponies Nov 17 '11 at 05:56

2 Answers2

2
SELECT *
FROM table1
LEFT OUTER JOIN table2 ON ( table1.name = table2.name )
LEFT OUTER JOIN table3 ON ( table1.name = table3.name )
Utku Yıldırım
  • 2,277
  • 16
  • 20
0
 -- Enhance table
 alter table table1 add (field2 /*e.g.*/ varchar2(10)
                        ,field3 /*e.g.*/ varchar2(10) );

 -- update rows roughly works like this. (I don't exactly know your column names, primary keys, etc)
 update table1 o set o.field2 = (select i.field from table2
                                 where o.field1 = i.field);