CODEWITHSUNDEEP
mysqlrelational-database
voteforpedro
voteforpedro

Reputation: 187

Many-to-many join table, bi-directional relationships and data integrity

I have an old legacy table like this:

 =====================
| product1 | product2 |
 =====================
|        1 |        2 |
|        2 |        1 |
|        3 |        4 |
|        4 |        5 |
|        5 |        3 |
 =====================

It's a poorly designed table, I know, but it has to stay. The purpose of the table is to store relationships between products. The rule is that each product should have a reference to each similar product and vice-versa. Assuming that the example data is the full dataset, the first two rows are correct - 1 has a relationship with 2 and 2 has a relationship with 1. However, the last three rows are a mess. The correct way of representing those products' relationships would be:

 =====================
| product1 | product2 |
 =====================
|        3 |        4 |
|        3 |        5 |
|        4 |        3 |
|        4 |        5 |
|        5 |        3 |
|        5 |        4 |
 =====================

What I need is some kind of algorithm (or tool) so that I can identify and fix the incorrect data.

Upvotes: 0

Views: 104

Answers (1)

Erwin Smout
Erwin Smout

Reputation: 18408

That's a relatively straightforward SQL query.

SELECT product1,product2 ... WHERE its mirror row does not exist.

and that latter clause is like

NOT EXISTS ( SELECT * FROM tbl AS MIRROR WHERE MIRROR.product1 = outertbl.product2 AND MIRROR.product2 = outertbl.product1 )

If MySQL supports EXCEPT, that's another way to write the query.

Upvotes: 2

Related Questions