How to use a MySQL column alias for calculations?

Question

How can I use my column alias (lat and lng) from the two subqueries to make the distance calcuation underneath? What I am basically trying to do is is to calculate the distance between two locations using longitude and latitude values. But somehow my aliases aren't usable in the query, 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

Does it run without any errors or do you get some errors while executing this query? — Lion, Dec 30 '11 at 15:11
The quotes are needed to run the query, I get no errors and everything is returned correctly except that the distance calculation returns the same value for all records, meaning that the values entered are not used for anything. What am I doing wrong here? — chrismaaz, Dec 30 '11 at 15:42

Ben D · Accepted Answer · 2012-01-06T15:49:20.423

4

OK, What you need to do here is join the same table (wp_postmeta) twice under different aliases so you can use different 'WHERE' conditions. I don't have your tables so I can't test this, but this is the approach you'll 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 in there, but I believe that the logic is roughly correct. Let me know if this works.

edited Jan 06 '12 at 15:49

answered Jan 05 '12 at 15:26

Ben D

14,321
3
45
59

1

Thanks for your suggestions! I tried your query but get the same error as before. **Unknown column 'lat' in 'field list'**. Is it possible that aliases can't be referenced in calculations? Seems dodgy to me. – chrismaaz Jan 06 '12 at 15:44
Hmm... I doubt it, but go ahead and edit the query so that cos( radians( `lat` ) ) becomes cos( radians( `alias_1`.meta_value ) ) etc and see what happens. Also, I enclosed lat and lng in backticks just for good measure. – Ben D Jan 06 '12 at 15:52
I think that did it! I am getting a working query now, and the distance column is giving me results now, although I am not sure they are correct. One of the results reads out "5.89936971664429e-05", which seems strange? – chrismaaz Jan 06 '12 at 15:56
I think this value comes from it including the same location to calculate the distance which screws it up. I'll fix this by making sure it excludes the originating location. Thanks! – chrismaaz Jan 06 '12 at 16:08

Ben D · Answer 2 · 2011-12-30T16:04:35.130

1

Once you've created your alias (which you should add quotes before and after) you should not reference it as a string... subsequent references to the alias should be enclosed in backticks:

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

edited Dec 30 '11 at 16:04

answered Dec 30 '11 at 15:49

Ben D

14,321
3
45
59

I am getting the error "Unknown column 'lat' in 'field list'". I guess the aliases can't be referenced like this? – chrismaaz Dec 30 '11 at 15:58
Ah! You have another problem as well. You should't have multiple select statements unless you're running a subquery. Just use "SELECT wp_posts.*, wp_postmeta.meta_value as 'lat', wp_postmeta.meta_value as `lng` FROM wp_posts, wp_postmeta WHRE [LIST ALL YOUR WHERE CONDITIONS) – Ben D Dec 30 '11 at 16:06
But I need the values from those fields (lat / lng) for the distance calculation - hence the subqueries. – chrismaaz Dec 30 '11 at 16:26
Anyone got any more ideas why the aliases aren't working? Thank you! – chrismaaz Dec 30 '11 at 19:46
Would a COALESCE subfunction help here? I am at a loss and would **really appreciate** some help here. Thanks! – chrismaaz Jan 05 '12 at 14:16

How to use a MySQL column alias for calculations?

2 Answers2