MySQL Forums
Forum List  »  Newbie

Re: SQRT / Pythagoras Query problem
Posted by: gary campbell
Date: May 15, 2014 03:12AM

Thanks for your reply Peter.

After a late night / early morning researching I'm now closer to a solution. The query I've come up with (based on the above data) is:

CREATE procedure `whatever` (b_name varchar(45), a_name varchar(45), a_address varchar(45), Distance decimal(3,2))

BEGIN

SELECT b.b_Name as b_name, a.a_Name as a_name, a.a_address as a_address, SQRT(((b.b_X_coord - c.a_X_coord) * (b.b_X_coord - c.a_X_coord)) + ((b.b_Y_coord - c.a_Y_coord) * (b.b_Y_coord - c.a_Y_coord))) as Distance

FROM Loc a, Loc b, Loc c
WHERE b.Loc = '<b_name value>' (this is the user defined choice)
AND a.Loc = c.Loc
AND Distance <= 10

ORDER BY Distance asc

END

I think this will return values into a table described in my original post, and calculates the distances from the ALL 'a_name' to the single user-defined 'b_name', but only returning those whose distance is less than or equal to 10.

Howver, there is 1 error returned, and that, (in workbench) is indicated by the red cross next to the line containing END. Any ideas?

Options: ReplyQuote


Subject
Written By
Posted
Re: SQRT / Pythagoras Query problem
May 15, 2014 03:12AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.