Join from Multiple Tables

[mikawin]

Hello,

I have a table with two columns in a table with data like:

Col1 Col2
1 13172
1 13934
1 15030
1 15544
1 16007
1 16326
1 16357
1 17153
...
...
...


I want to expand this table with data from 4 other tables (t1-t4) which contain the above two columns + other attributes.

My output should look like the sample below (which is tab-delimited)

Col1 Col2 t1.col t2.col t3.col t4.col

1 13172 t1.*
1 15030 t1.* t2.* t3.*
1 13172 t2.* t4.*
1 13934 t2.* t3.*
1 15030 t1.* t2.* t3* t4.*
1 15544 t2.* t3*
1 16007 t1.* t3.*
....
....
....

Thank you for your help!

~Mika

 

[SantaMufasa]

Mikawin,

You results are wonky...you have two lines for 13172 and two lines for 15030. It's difficult to produce code that matches your results when your results are illogical.

If you can please post "CREATE TABLE..." and "INSERT" statements for the tables "t0" through "t4", it will be much easier for us to post a sample solution for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[mikawin]

Sorry SantaMufasa,

I created an arbitrary 2nd table and did not realize that I had duplicated rows.

So the data from 1st table is:

Col1 Col2
1 13172
1 13934
1 15030
1 15544
1 16007
1 16326
1 16357
1 17153

My output should be (sans duplicate rows) is:

Col1 Col2 t1.col t2.col t3.col t4.col

1 13172 t2.* t4.*
1 13934 t2.* t3.*
1 15030 t1.* t2.* t3* t4.*
1 15544 t2.* t3*
1 16007 t1.* t3.*


If it is two tables, I can successfully implement 'LEFT OUTER JOIN'. But I was wondering how can I extend this to data from 4 tables.

Thanks,
~ Mika

 

[carp]

When in doubt, try it!

SELECT a.col1, a.col2, 
       t1.col, t2.col, t3.col, t4.col
FROM firstTable a
     LEFT OUTER JOIN table1 t1
        ON ...
     LEFT OUTER JOIN table2 t2
        ON ...
     LEFT OUTER JOIN table3 t3
        ON ...
     LEFT OUTER JOIN table4 t4
        ON ...
WHERE ....