Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
My SQL Hell
Starting Member
3 Posts |
 Posted - 2014-05-14 : 06:18:30
|
| Hi, can someone please put me out of my misery. I have been trying to teach myself databasing (don't ask why), and have so far succeeded at a very basic level. However, I am now, and have been for weeks, stuck on this probably quite simple problem:I'm using MySQL Workbench and server v.6.1I have created several tables which feed data into the table to which this query relates - lets call it LOC. This table's columns are:a_ID | a_name | a_address | a_X_coord | a_Y_coord | b_ID | b_Name | b_X_coord | b_Y_coord-----------------------------------------------------------------------------NOTE: All the coords are simple single digit X&Y's (eg X=2, Y=8), NOT geolocation coords. I'll move onto more accurate / complex geolocational problems using radians / COS etc after I've got the basics.The problem: I'm trying to create a query that, when a user selects any 'b_Name', the query returns all the 'a' data (but not the coords) with their distances from the selected 'b' location in a table that will look like:b_Name | a_Name | a_address | Distance_of_a_from b |----------------------------------------------The formula I'm using to calculate a's distance from b is pythagoras: SQRT(((a_X - b_X) * (a_X - b_X)) + ((a_Y - b_Y) * (a_Y - b_Y))) which I think is correct. 1. Should this be a FUNCTION or PROCEDURE query?2. I've lost count of my efforts to create this query and all have returned syntax errors and no amount of tweaking rectifies it, hence why I'm turning to the experts for help.Any help will be greatly appreciated. |
|
|
My SQL Hell
Starting Member
3 Posts |
Posted - 2014-05-14 : 09:52:51
|
| If it helps identify where I'm going wrong, here is my latest attempt and the query: DELIMITER $$CREATE procedure `Hotels_Within_15_miles` (Attraction varchar(45), Hotel varchar(45), Address varchar(45), Telno int, Distance decimal(3,2))beginSELECT 'T_Name' AS Attraction, A_Name AS Hotel, A_Address AS Address, A_Telno AS Telno, SQRT(((T_Lat - A_Lat) * (T_Lat - A_Lat)) + ((T_Lon - A_Lon) * (T_Lon - A_Lon))) AS DistanceFROM Locational_Data WHERE Distance <= 15Order by Distance asc END The error is showing at the line containing END. Additionally, the <=15 is because I only want returns for those hotels located within 15 miles of the attraction. |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-05-14 : 12:07:18
|
| Someone might be able to help you, but this is a MS SQL Server specific forum. So, you might get better/more timely help from a My SQL forum. |
|
|
|
My SQL Hell
Starting Member
3 Posts |
Posted - 2014-05-14 : 12:15:02
|
quote:
Originally posted by Lamprey
Someone might be able to help you, but this is a MS SQL Server specific forum. So, you might get better/more timely help from a My SQL forum.
Quite right, my mistake. Thanks Lamprey |
|
|
|
|
|
|
|
|
SQRT / Pythagoras help please