Enayet Hussain Enayet Hussain - 1 year ago 84
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.


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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download