How to solve unknown alias column issue without using nested queries?

Question

I've the query

SELECT ID, 
  post_title, 
  post_author,
  max(case when meta_key='geo_latitude' then meta_value end) latitude,
  max(case when meta_key='geo_longitude' then meta_value end) longitude,
 ( 3959 * acos( cos( radians(18.204540500000) ) 
                   * cos( radians( latitude ) ) 
                   * cos( radians( longitude ) 
                       - radians(-66.450958500000) ) 
                   + sin( radians(18.204540500000 ) )
                   * sin( radians( latitude ) ) 
                 )
   ) AS distance 
FROM `wp_posts` 
LEFT JOIN `wp_postmeta` 
  on ID=post_id 
WHERE post_type='place' 
  AND (meta_key='geo_latitude' OR meta_key='geo_longitude') 
GROUP BY ID, post_title, post_author
ORDER BY ID ASC

which results in

Error Code: 1054. Unknown column 'latitude' in 'field list'

Is there any way to solve this without using inner/nested queries?

The only way is nested sql, if you had analytic functions available to you in mysql then there would be an alternative approach using max(...) over (partition by ...) — Andrew, Nov 06 '12 at 12:43
@Andrew I also thought it is not possible, but it is simple see the accepted answer — Mithun Sreedharan, Nov 07 '12 at 05:59

score 1 · Answer 1 · answered Nov 06 '12 at 13:09

You can create a view:

create view geo as
    select id, post_title, post_author,
           max(case when meta_key='geo_latitude' then meta_value end) as latitude,
           max(case when meta_key='geo_longitude' then meta_value end) as longitude
    from wp_posts
    where post_type='place'
          and (meta_key='geo_latitude' OR meta_key='geo_longitude')
    group by id, post_title, post_author;

and join your query with it instead of wp_posts:

SELECT ID, 
  post_title, 
  post_author,
  latitude,
  longitude,
 ( 3959 * acos( cos( radians(18.204540500000) ) 
                   * cos( radians( latitude ) ) 
                   * cos( radians( longitude ) 
                       - radians(-66.450958500000) ) 
                   + sin( radians(18.204540500000 ) )
                   * sin( radians( latitude ) ) 
                 )
   ) AS distance 
FROM `geo` 
LEFT JOIN `wp_postmeta` 
  on ID=wp_postmeta.post_id 
ORDER BY ID ASC;

score 1 · Accepted Answer · answered Nov 06 '12 at 14:12

Try this:

SELECT ID, 
  post_title, 
  post_author,
  MAX(CASE WHEN meta_key='geo_latitude' THEN meta_value END) latitude,
  MAX(CASE WHEN meta_key='geo_longitude' THEN meta_value END) longitude, 
  ( 3959 * ACOS( COS( RADIANS(18.204540500000) ) 
                   * COS( RADIANS( MAX(CASE WHEN meta_key='geo_latitude' THEN meta_value END) ) ) 
                   * COS( RADIANS( MAX(CASE WHEN meta_key='geo_longitude' THEN meta_value END) ) 
                       - RADIANS(-66.450958500000) ) 
                   + SIN( RADIANS(18.204540500000 ) )
                   * SIN( RADIANS( MAX(CASE WHEN meta_key='geo_latitude' THEN meta_value END) ) ) 
                 )
   ) AS distance  
FROM `wp_posts` 
LEFT JOIN `wp_postmeta` 
  ON ID=post_id 
WHERE post_type='place' 
  AND (meta_key='geo_latitude' OR meta_key='geo_longitude') 
GROUP BY ID, post_title, post_author
ORDER BY ID ASC

Also I can remove the `MAX.. AS latitude` lines from SELECT list for faster performance — Mithun Sreedharan, Nov 07 '12 at 05:57

How to solve unknown alias column issue without using nested queries?

2 Answers2