How to use MySQL column alias for calculations?

Question

How can I use my column alias (lat and lng) from two subqueries to make the distance calculation lower? What I'm basically trying to do is calculate the distance between two points using the longitude and latitude values. But for some reason, my aliases cannot be used in the request, why?

SELECT wp_posts.*, (SELECT wp_postmeta.meta_value FROM wp_postmeta WHERE wp_postmeta.post_id = wp_posts.ID AND wp_postmeta.meta_value LIKE '41.%') AS lat, (SELECT wp_postmeta.meta_value FROM wp_postmeta WHERE wp_postmeta.post_id = wp_posts.ID AND wp_postmeta.meta_value LIKE '2.%') AS lng, (3959 * acos( cos( radians(41.367682) ) * cos( radians( 'lat' ) ) * cos( radians('lng') - radians(2.154077)) + sin(radians(41.367682)) * sin( radians('lat')))) AS distance FROM wp_posts, wp_postmeta WHERE wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'position' AND wp_posts.post_status = 'publish' AND wp_posts.post_type = 'page' AND wp_posts.post_date < NOW() GROUP BY ID ORDER BY distance ASC 

Answer 1

OK. What you need to do is join the same table (wp_postmeta) twice under different aliases so that you can use different WHERE conditions. I do not have your tables, so I can not check this, but this is the approach you want to use:

 SELECT wp_posts.*, (`alias_1`.meta_value) AS `lat`, (`alias_2`.meta_value) AS `lng`, (3959 * acos( cos( radians(41.367682) ) * cos( radians( `lat` ) ) * cos( radians( `lng` ) - radians(2.154077)) + sin(radians(41.367682)) * sin( radians( `lat` )))) AS `distance` FROM wp_posts LEFT JOIN `wp_postmeta` AS `alias_1` ON wp_posts.ID = alias_1.post_id LEFT JOIN `wp_postmeta` AS `alias_2` ON wp_posts.ID = alias_2.post_id WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type = 'page' AND wp_posts.post_date < NOW() AND `alias_1`.meta_key = 'position' AND `alias_1`.meta_value LIKE '41.%' AND `alias_2`.meta_key = 'position' AND `alias_2`.meta_value LIKE '2.%' GROUP BY wp_posts.`ID` ORDER BY `distance` ASC 

I may have some syntax errors, but I think the logic is roughly correct. Let me know if this works.

Answer 2

Once you have created your alias (which you must add quotes before and after), you should not refer to it as a string ... the following links to the alias should be enclosed in backlinks:

 SELECT wp_posts.*, (SELECT wp_postmeta.meta_value FROM wp_postmeta WHERE wp_postmeta.post_id = wp_posts.ID AND wp_postmeta.meta_value LIKE '41.%') AS 'lat', (SELECT wp_postmeta.meta_value FROM wp_postmeta WHERE wp_postmeta.post_id = wp_posts.ID AND wp_postmeta.meta_value LIKE '2.%') AS 'lng', (3959 * acos( cos( radians(41.367682) ) * cos( radians( `lat` ) ) * cos( radians(`lng`) - radians(2.154077)) + sin(radians(41.367682)) * sin( radians(`lat`)))) AS `distance` FROM wp_posts, wp_postmeta WHERE wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'position' AND wp_posts.post_status = 'publish' AND wp_posts.post_type = 'page' AND wp_posts.post_date < NOW() GROUP BY `ID` ORDER BY 'distance' ASC 

http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html