Table alias -- Unknown column in field list

Question

I have a sql query which is executing a LEFT JOIN on 2 tables in which some of the columns are ambiguous. I can prefix the joined tables but when I try to prefix one of the columns from the table in the FROM clause, it tells me Unknown column. I even tried giving that table an alias like so ...From points AS p and using "p" to prefix the tables but that didn't work either. What am I doing wrong? Here is my query:

SELECT point_title, point_url, address, city, state, zip_code, 
     phone, `points`.`lat`, `points`.`longi`, featured, 
     kmlno, image_url, category.title, category_id, 
     point_id, lat, longi, reviews.star_points, reviews.review_id,
     count(reviews.point_id) as totals 
FROM (SELECT *, 
             ( 3959 * acos( cos( radians('37.7717185') ) * cos( radians( lat ) ) 
               * cos( radians( longi ) - radians('-122.4438929') ) 
               + sin( radians('37.7717185') ) * sin( radians( lat ) ) ) ) AS distance 
        FROM points 
      HAVING distance < '25') as distResults 
LEFT JOIN category USING ( category_id ) 
LEFT JOIN reviews USING ( point_id ) 
    WHERE  (point_title LIKE '%Playgrounds%' OR category.title LIKE '%Playgrounds%') 
 GROUP BY point_id 
 ORDER BY totals DESC, distance
    LIMIT 0, 10

Here is the schema for the tables involved:

Points table: Field Type Null Default Comments point_id int(11) No
point_title varchar(255) No
category_id int(11) No
description text No
point_url varchar(255) No
address varchar(255) No
city varchar(255) No
state varchar(255) No
zip_code varchar(255) No
phone varchar(255) No
filename varchar(255) No
lat varchar(25) No
longi varchar(25) No
user_id int(2) No 0
featured int(1) No 0
status int(1) No 1
kmlno int(2) No 0
lastupdate varchar(255) No
image_url varchar(255) No

Reviews table: Field Type Null Default Comments review_id int(11) No
review int(1) No 0
review_title varchar(255) No
review_desc text No
star_points int(11) No 0
user_id int(11) No 0
point_id int(11) No
lat varchar(25) No
longi varchar(25) No
lastupdate varchar(255) No
status varchar(1) No r

Category Table: Field Type Null Default Comments category_id int(11) No
title varchar(255) No
description text No
filename varchar(255) No
image_name varchar(255) No
status int(1) No 0
lastupdate varchar(255) No

Which column is giving you the problem? Can you format your query so that it's possible to read it? — Mark Byers
I figured it out. Instead of prefacing the lat and longi columns with 'points' I needed to preface with 'distResults' since that is the table alias being used. Thanks for the help everyone. — Jason

mdma mdma · Accepted Answer · 2010-05-22T20:56:13

Without seeing your schema it's difficult to know for sure what the problem is. But I do see one problem - move the HAVING distance < 25 from the inner select to the outer select. The "distance" name is not available to the inner select. I've also changed HAVING to WHERE, since there was no GROUP BY.

SELECT point_title, point_url, address, city, state, zip_code, 
     phone, `points`.`lat`, `points`.`longi`, featured, 
     kmlno, image_url, category.title, category_id, 
     point_id, lat, longi, reviews.star_points, reviews.review_id,
     count(reviews.point_id) as totals 
FROM (SELECT * FROM (SELECT *, 
        ( 3959 * acos( cos( radians('37.7717185') ) * cos( radians( lat ) ) 
           * cos( radians( longi ) - radians('-122.4438929') ) + 
             sin( radians('37.7717185') ) * sin( radians( lat ) ) ) ) AS distance 
      FROM points) AS distResultsInner
      WHERE distance<25)) AS distResults
LEFT JOIN category USING ( category_id ) 
LEFT JOIN reviews USING ( point_id ) 
WHERE  (point_title LIKE '%Playgrounds%' 
    OR category.title LIKE '%Playgrounds%') 
GROUP BY point_id ORDER BY totals DESC, distance 
LIMIT 0 , 10

Table alias -- Unknown column in field list

1 Answers