(SQL) Create Table with Distances from Separate Table with XY Coordinates

I am trying to create a table that will have unique distances between slot machines based on the following dataset.

I know I first need to calculate my distances and the only way I currently know how would be to use the following code:

select (SELECT abs(p1.coordinate_x - p2.coordinate_x) + abs(p1.coordinate_y - p2.coordinate_y)) as Distance
FROM dim_slot_coordinates p1
CROSS JOIN dim_slot_coordinates p2
WHERE p1.slot_machine_id = 1 and p2.slot_machine_id = 2

But this obviously only returns the distance between the first two machines. How do I calculate the distances between all of the machines? Then, how would I properly make sure each distance is unique?

What would be the easiest way to do this in SQL?

Thank you for any help!

Answer

You would simply use a cross join:

select p1.slot_machine_id, p2.slot_machine_id,
       (abs(p1.coordinate_x - p2.coordinate_x) + abs(p1.coordinate_y - p2.coordinate_y)) as Distance
from dim_slot_coordinates p1 CROSS JOIN 
     dim_slot_coordinates p2;

If you wanted the distance between two machines only calculated once, then add an inequality:

select p1.slot_machine_id, p2.slot_machine_id,
       (abs(p1.coordinate_x - p2.coordinate_x) + abs(p1.coordinate_y - p2.coordinate_y)) as Distance
from dim_slot_coordinates p1 CROSS JOIN 
     dim_slot_coordinates p2
where p1.slot_machine_id < p2.slot_machine_id;

EDIT:

If you want “unique” distances, you could use:

select distinct p1.slot_machine_id
       (abs(p1.coordinate_x - p2.coordinate_x) + abs(p1.coordinate_y - p2.coordinate_y)) as Distance
    from dim_slot_coordinates p1 CROSS JOIN 
         dim_slot_coordinates p2
    where p1.slot_machine_id <> p2.slot_machine_id;

This doesn’t seem useful to me, but it does provide a list of unique distances for each machine.