#1052 - Column 'lat' in field list is ambiguous in a mile radius query, can't figure out why

Question

I've got a rather lengthy query i have been working with that is throwing the error '#1052 - Column 'lat' in field list is ambiguous'. I have broken it into parts and each part seems to work fine but when I run it at once I get this error. Here is the query:

    SELECT lesson_requests_global_2.student_name,
           (3959 * ACOS(COS(RADIANS(30.096595)) * COS(RADIANS(lat)) * COS(RADIANS(lng) - RADIANS(- 81.718983)) + SIN(RADIANS(30.096595)) * SIN(RADIANS(lat)))) AS distance,
           lesson_requests_vendor.user_purchased
    FROM lesson_requests_global_2
    INNER JOIN
      ( SELECT student_name,
               MAX(request_date) AS max_request_date
       FROM lesson_requests_global_2
       WHERE ( 3959 * ACOS(COS(RADIANS(30.096595)) * COS(RADIANS(lat)) * COS(RADIANS(lng) - RADIANS(- 81.718983)) + SIN(RADIANS(30.096595)) * SIN(RADIANS(lat))) ) < 30
       GROUP BY student_name ) AS recent_student_lesson_request ON lesson_requests_global_2.student_name = recent_student_lesson_request.student_name
    AND lesson_requests_global_2.request_date = recent_student_lesson_request.max_request_date
    LEFT JOIN lesson_requests_vendor ON v.user_purchased = lesson_requests_global_2.student_name
    WHERE lesson_requests_vendor.user_purchased <> 'bob jones'
      AND distance < 30
    ORDER BY distance LIMIT 0 , 20

Please note that the long COS/RADIANS stuff looks complicated but it is to find a mile radius distance. I think that somehow it is thinking 'lat' within those formulas is somehow in the column list?

Thanks in advance for your help!

Have you looked at http://stackoverflow.com/questions/431391/php-mysql-how-to-resolve-ambiguous-column-names-in-join-operation? — j08691, Jun 15 '12 at 15:00
please see my comment to DonCallisto's answer. I think the issue here is with the mile radius formula which includes 'lat' in it but lat is not a column in the tables I am pulling. I am only using that part of the code to calculate distance — rob melino, Jun 15 '12 at 15:24

DonCallisto · Accepted Answer · 2012-06-15T15:05:54.703

0

Is very simple.

You join on the same table from where you select, so you'll have two column with the same name. If you don't put the "table name" before your field name, this will produce a sql error.

You can do something like this:

SELECT .... FROM lesson_requests_global_2 request
INNER JOIN
( SELECT ..... FROM lesson_request_globals_2 .....)
....
WHERE ....

and rename every occurrence of lat in request.lat

request is now an alias from your table name: "virtually" the first you're choosing from.

edited Jun 15 '12 at 15:05

answered Jun 15 '12 at 15:00

DonCallisto

29,419
9
72
100

thanks for replying. the problem is, the word 'lat' is not from my table, it is part of the Haversine formula so if i understand it correctly it is only used to calculate distance and not part of the table structure. i tried your suggestion above (renaming lat to request.lat and i didn't have any luck. any other ideas? – rob melino Jun 15 '12 at 15:15

score 0 · Answer 2 · answered Jun 15 '12 at 15:00

Sounds like both lesson_requests_global_2 and lesson_requests_vendor have a column called 'lat'. You need to specify which table you want to query it from:

SELECT lesson_requests_global_2.student_name,
       (3959 * ACOS(COS(RADIANS(30.096595)) * COS(RADIANS(lesson_requests_global_2.lat)) * COS(RADIANS(lng) - RADIANS(- 81.718983)) + SIN(RADIANS(30.096595)) * SIN(RADIANS(lat)))) AS distance,
       lesson_requests_vendor.user_purchased
FROM lesson_requests_global_2
INNER JOIN
  ( SELECT student_name,
           MAX(request_date) AS max_request_date
   FROM lesson_requests_global_2
   WHERE ( 3959 * ACOS(COS(RADIANS(30.096595)) * COS(RADIANS(lesson_requests_global_2.lat)) * COS(RADIANS(lng) - RADIANS(- 81.718983)) + SIN(RADIANS(30.096595)) * SIN(RADIANS(lesson_requests_global_2.lat))) ) < 30
   GROUP BY student_name ) AS recent_student_lesson_request ON lesson_requests_global_2.student_name = recent_student_lesson_request.student_name
AND lesson_requests_global_2.request_date = recent_student_lesson_request.max_request_date
LEFT JOIN lesson_requests_vendor ON v.user_purchased = lesson_requests_global_2.student_name
WHERE lesson_requests_vendor.user_purchased <> 'bob jones'
  AND distance < 30
ORDER BY distance LIMIT 0 , 20

#1052 - Column 'lat' in field list is ambiguous in a mile radius query, can't figure out why

2 Answers2