tabaluga tabaluga - 1 month ago 12
MySQL Question

Remove duplicates of pairs from multiple columns

Part of my table looks like this:



+-------------+-----------+-------------+
| FN1 | LN | FN2 |
+-------------+-----------+-------------+
| Geraint | Davies | David T. C. |
| Philip | Davies | David T. C. |
| David T. C. | Davies | Philip |
| David T. C. | Davies | Geraint |
| Mr Nigel | Evans | Graham |
| Graham | Evans | Mr Nigel |





It is made from joint tables where the join condition is last name (different people may have the same last names but different first names).

My question is how do I get rid of duplicates so there is always only one pair (using either FN1 or FN2)?

Answer

Change the query that creates the table so that is uses

t1.FN < t2.FN

instead of

t1.FN != t2.FN

This will prevent it from returning the related rows twice.