MySql: Find table2.name WHERE table1.lat = table2.lat AND table1.lng = table2.lng;

I have spent the last few days bashing my head in trying to find the answer to this. I have a Table of Routes of GPS data. and a Table of Points. What I need is a MySql statement that for a given route, will tell me the Point.Name of both end points. I could write 4 separate statements, but I believe there is a better way.

The tables have Similar columns {rowid, userid, latitude, longitude} to make things interesting:

Route.Name = '2011-06-27';

and

Point.Name = 'location_1';

To get the first and last points is easy. (Add DESC before LIMIT for last point )

SELECT `rowid`, `latitude`, `longitude` FROM `$db`.`Route` 
 WHERE `userid` LIKE '$userid' AND `name` = '$name' ORDER BY `rowid`  LIMIT 1;

I have been Reading about OUTER JOIN and thought that would work, this is as close as I have gotten.

SELECT DISTINCT a.`rowid`, a.`latitude`, a.`longitude`, b.`name` 
 FROM `$db`.`Route` a
 RIGHT OUTER JOIN `$db`.`Point` b ON a.`latitude` = b.`latitude`
 AND a.`longitude` = b.`longitude` WHERE a.`userid` LIKE '$userid'
 AND a.`name` = '$name';

Desired Output:

rowid | latitude | longitude | name

'16660', '49.22551', '-122.57416', 'location_5'

'16792', '49.16722109', '-122.98667883', 'location_1'

Actual Output:

rowid | latitude | longitude | name

'16791', '49.16721654', '-122.98723093', 'location_1'

'16792', '49.16722109', '-122.98667883', 'location_1'

I will be very thankful of any assistance in helping me solve this issue. Let me know if further detail is needed.

==== EDIT ====

SELECT `name`, `latitude`, `longitude` FROM `$db`.`Point`
 WHERE (`latitude`,`longitude`) IN(
 SELECT `rowid`, `latitude`, `longitude` FROM 
 ( SELECT `rowid`, `latitude`, `longitude` FROM `$db`.`Route` 
 WHERE `userid` LIKE '$userid' AND `name` = '$name' ORDER BY `rowid`  LIMIT 1) as t1
UNION
 ( SELECT `rowid`, `latitude`, `longitude` FROM `$db`.`Route` 
 WHERE `userid` LIKE '$userid' AND `name` = '$name' ORDER BY `rowid` DESC LIMIT 1) )

The ca开发者_运维技巧tch is this generates Error Code: 1235 This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

So I'll need to experiment a bit more on getting results w/o using LIMIT or retrieve the results and then generate a second query using the results. I'll get back to you later. Thank you Again.

You can use a UNION to combine the two SQLs.

SELECT `rowid`, `latitude`, `longitude` FROM 

( SELECT `rowid`, `latitude`, `longitude` FROM `$db`.`Route` 
 WHERE `userid` LIKE '$userid' AND `name` = '$name' ORDER BY `rowid`  LIMIT 1) AS t0
UNION
( SELECT `rowid`, `latitude`, `longitude` FROM `$db`.`Route` 
 WHERE `userid` LIKE '$userid' AND `name` = '$name' ORDER BY `rowid` DESC LIMIT 1) AS t1

I broke down and separated the statements into one to collect the endpoints, and then I'll loop the results to query the names. I really would have liked to get all the results at once, to cut down on traffic dealing with a large number of users =\

SELECT TRUNCATE(`latitude`,3) AS latitude, TRUNCATE(`longitude`,3) AS longitude 
 FROM `$db`.`$table1`
 WHERE `rowid` = (
  SELECT MIN(`rowid`) 
  FROM `$db`.`$table1`
  WHERE `userid` LIKE '$userid' AND `name` = '$name') 
UNION
 SELECT TRUNCATE(`latitude`,4) AS latitude, TRUNCATE(`longitude`,3) AS longitude
 FROM `$db`.`$table1`
 WHERE `rowid` = (
  SELECT MAX(`rowid`) 
  FROM `$db`.`$table1`
  WHERE `userid` LIKE '$userid' AND `name` = '$name')

Loop the results from the previous query with this statement to get the points to display end points.

SELECT name, latitude, longitude FROM `$db`.`$table2`
WHERE TRUNCATE(latitude,3) ='$lat' AND TRUNCATE(longitude,3) ='$lng' LIMIT 1;