Below is what I have in the table 'HOUSE_LOCATIONS':
| OWNER | X | Y |
|---|---|---|
| Carlos | 4 | 2 |
| Peter | 1 | 2 |
| Ben | 6 | 3 |
| Jenny | 3 | 5 |
What I have to do is set Carlos as the datum point
SET @carlosX = (SELECT x FROM HOUSE_LOCATIONS WHERE OWNER = 'Carlos');
SET @carlosY = (SELECT y FROM HOUSE_LOCATIONS WHERE OWNER = 'Carlos');
and calculate the distance between each person: abs(@carlosX - X, @carlosY - Y) and sum them.
What I need is:
| OWNER | DISTANCE |
|---|---|
| Jenny | 4 |
| Peter | 3 |
| Ben | 3 |
I tried to make a query, but I guess it's a bit complex for me.
CodePudding user response：
Use a self join:
SELECT h1.OWNER,
ABS(h2.X - h1.X) ABS(h2.Y - h1.Y) DISTANCE
FROM HOUSE_LOCATIONS h1 INNER JOIN HOUSE_LOCATIONS h2
ON h2.OWNER <> h1.OWNER
WHERE h2.OWNER = 'Carlos';
See the demo.