sql - Many to Many join with the least duplicate rows -

sql - Many to Many join with the least duplicate rows -

lets have 2 tables. each table has id column.multiple rows in each table may have same id. need bring together tables on id selecting each record in each table once. there many many relationship between tables. using oracle database.

lets have 2 tables.

table 1:

id | office address ------------------- 1 | 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 want is:

id | company address | company name ---------------------------------------------- 1 | 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 span query on 10 different tables, total of 300 columns, not having specify each column nice.

try query:

select coalesce(t1.id, t2.id) id, t1.office_address, t2.company_name ( select t1.*, row_number() on (partition id order id) rn table1 t1 ) t1 total outer bring together ( select t2.*, row_number() on (partition id order id) rn table2 t2 ) t2 on t1.id = t2.id , t1.rn = t2.rn order id

test on sql fiddle

sql oracle join