Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Join from Multiple Tables

Status
Not open for further replies.

mikawin

Technical User
Apr 15, 2009
28
US
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
 
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.

[santa]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.”
 
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

 
When in doubt, try it!
Code:
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 ....
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top