Enayet Hussain Enayet Hussain - 4 days ago 5
MySQL Question

MySQL Join each row to every row except itself

I have a single table that I need to join to itself but each entry needs to be joined to every entry in the table except to itself.

Example:

my_table
tableId name
1 John
2 Dave
3 Bob


Output from join:

tableIdA nameA tableIdB nameB
1 John 2 Dave
1 John 3 Bob
2 Dave 1 John
2 Dave 3 Bob
3 Bob 1 John
3 Bob 2 Dave


How would I achieve such an output?

Answer

You can use a CROSS JOIN in order to get all combinations. Then use a WHERE clause to filter out combinations between the same rows:

SELECT t1.*, t2.*
FROM mytable AS t1
CROSS JOIN mytable AS t2
WHERE t1.Id <> t2.ID
Comments