Lets say I have two tables. Each table has an ID column.Multiple rows in each table may have the same ID. I need to join the tables on ID selecting each record in each table only once. There is a many to many relationship between the tables. Using Oracle database.
Lets say we have two tables.
Table 1:
ID | Office Address
-------------------
1 | A Street
1 | B Street
2 | C street
3 | D Street
Table 2:
ID | Company Name
------------------
1 | Toys Limited
1 | Toys Incorporated
1 | Toys Unlimited
2 | Best Cakes
4 | Best Boxes
and what I want to get is:
ID | Company Address | Company Name
----------------------------------------------
1 | A Street | Toys Limited
1 | B Street | Toys Incorporated
1 | Null (some value acceptable) | Toys Unlimited
2 | C Street | Best Cakes
3 | D Street | Null
4 | Null | Best Boxes
I need to span this query over 10 different tables, with a total of 300 columns, so not having to specify each column would be nice.
Try this query:
SELECT COALESCE(t1.id, t2.id) AS ID, t1.Office_Address, t2.Company_Name
FROM (
SELECT t1.*, ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS rn
FROM Table1 t1
) t1
FULL OUTER JOIN (
SELECT t2.*, ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS rn
FROM Table2 t2
) t2 ON t1.id = t2.id AND t1.rn = t2.rn
ORDER BY ID
Test it on SQL Fiddle
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments