Many to Many join with the least duplicate rows

user1488773

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.

Rimas

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.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Remove duplicate rows on many to many table (Mysql)

From Dev

many to many query mysql return duplicate rows

From Dev

Remove duplicate rows on many to many table (Mysql)

From Dev

Prevent duplicate entries in a join table in a many-to-many relationship in JPA

From Dev

Prevent duplicate entries in a join table in a many-to-many relationship in JPA

From Dev

Entity Framework 6 Many-to-many wants to insert duplicate rows

From Dev

Entity Framework 6 Many-to-many wants to insert duplicate rows

From Dev

GreenDAO To many relations not adding rows to join class

From Dev

Mysql one to many join, rows to columns

From Dev

Printing duplicate rows as many times it is duplicate in the input file using UNIX

From Dev

Count how many rows have at least one certain value

From Dev

How to remove duplicate rows in Excel 2013 when there are many columns?

From Dev

Many to Many relation with join table

From Dev

Many to many join Php/mysql

From Dev

How to join many to many in createQuery()

From Dev

Doctrine many to many left join

From Dev

Duplicate entries in EF7 join table for many-to-many relationship

From Dev

SELECT, JOIN, and a WHERE != statement returning too many rows

From Dev

Optimize query contains too many inner join and rows

From Dev

mysql join one to many relationship and print in different rows

From Dev

Pandas: join multiple columns of one row to many rows (1:n)

From Dev

Redshift Query returning too many rows in aggregate join

From Dev

SqlAlchemy - Many to Many outer join with conditions on join

From Dev

SqlAlchemy - Many to Many outer join with conditions on join

From Dev

SQL One to many join - do not return rows from the 'one' table if ANY row in the 'many' table equals X

From Dev

Entity Framework duplicate entries in many to many relationship

From Dev

SQLAlchemy Many To Many relationship returning duplicate children

From Dev

mySQL JOIN on one to many

From Dev

Simplify many LINQ Join

Related Related

  1. 1

    Remove duplicate rows on many to many table (Mysql)

  2. 2

    many to many query mysql return duplicate rows

  3. 3

    Remove duplicate rows on many to many table (Mysql)

  4. 4

    Prevent duplicate entries in a join table in a many-to-many relationship in JPA

  5. 5

    Prevent duplicate entries in a join table in a many-to-many relationship in JPA

  6. 6

    Entity Framework 6 Many-to-many wants to insert duplicate rows

  7. 7

    Entity Framework 6 Many-to-many wants to insert duplicate rows

  8. 8

    GreenDAO To many relations not adding rows to join class

  9. 9

    Mysql one to many join, rows to columns

  10. 10

    Printing duplicate rows as many times it is duplicate in the input file using UNIX

  11. 11

    Count how many rows have at least one certain value

  12. 12

    How to remove duplicate rows in Excel 2013 when there are many columns?

  13. 13

    Many to Many relation with join table

  14. 14

    Many to many join Php/mysql

  15. 15

    How to join many to many in createQuery()

  16. 16

    Doctrine many to many left join

  17. 17

    Duplicate entries in EF7 join table for many-to-many relationship

  18. 18

    SELECT, JOIN, and a WHERE != statement returning too many rows

  19. 19

    Optimize query contains too many inner join and rows

  20. 20

    mysql join one to many relationship and print in different rows

  21. 21

    Pandas: join multiple columns of one row to many rows (1:n)

  22. 22

    Redshift Query returning too many rows in aggregate join

  23. 23

    SqlAlchemy - Many to Many outer join with conditions on join

  24. 24

    SqlAlchemy - Many to Many outer join with conditions on join

  25. 25

    SQL One to many join - do not return rows from the 'one' table if ANY row in the 'many' table equals X

  26. 26

    Entity Framework duplicate entries in many to many relationship

  27. 27

    SQLAlchemy Many To Many relationship returning duplicate children

  28. 28

    mySQL JOIN on one to many

  29. 29

    Simplify many LINQ Join

HotTag

Archive