I've got 2 tables: candidate and candidate_location. The candidate_location table gets map coordinates from either of 3 other tables depending on it's type field. The result are the aliases lat and lng which need to be used in the part of the query following the IF statements where it works out the distance.
I'm getting the error that it can't find the "lat" or "lng" column in the distance calculation part, I guess because they are aliases. How could I restructure this so they are recognised in the calculation?
Here's the query:
SELECT candidate.CandID,
IF (candidate_location.type = 1, p.latitude, IF (candidate_location.type = 2, a.latitude, pr.latitude)) AS lat,
IF (candidate_location.type = 1, p.longitude, IF (candidate_location.type = 2, a.longitude, pr.longitude)) AS lng,
IF (candidate_location.type = 2, a.standard_deviation,null) as standard_deviation,
( 3959 * acos( cos( radians(51.41019) ) * cos( radians(lat ) )
* cos( radians(lng) - radians(0.07222)) + sin(radians(51.41019))
* sin( radians(lat)))) AS distance
FROM candidate_location
LEFT JOIN geo_postcodes AS p ON (candidate_location.type = 1 AND candidate_location.postal_id = p.id)
LEFT JOIN geo_area_averages AS a ON (candidate_location.type = 2 AND candidate_location.postal_id = a.id)
LEFT JOIN geo_probability AS pr ON (candidate_location.type = 3 AND candidate_location.postal_id = pr.id)
LEFT JOIN candidate ON candidate_location.candid=candidate.CandID
As Ashwin mentioned, you can not use the result column name directly as a field in computation. You would need to restate it again... unless you alternatively use MySql variables. They work like inline programming. Set the variable value, then use that, but you can still retain that value AS the column name in question.
SELECT
candidate.CandID,
@varLat := COALESCE( p.latitude, a.latitude, pr.latitude) AS lat,
@varLong := COALESCE( p.longitude, a.longitude, pr.longitude) AS lng,
COALESCE( a.standard_deviation, null) as standard_deviation,
( 3959 * acos( cos( radians(51.41019) ) * cos( radians(@varLat ) )
* cos( radians(@varLat) - radians(0.07222)) + sin(radians(51.41019))
* sin( radians(@varLong)))) AS distance
FROM
( select @varLat := 0.00, @varLong := 0.00) sqlvars,
candidate_location CanLoc
LEFT JOIN geo_postcodes AS p
ON (CanLoc.type = 1 AND CanLoc.postal_id = p.id)
LEFT JOIN geo_area_averages AS a
ON (CanLoc.type = 2 AND CanLoc.postal_id = a.id)
LEFT JOIN geo_probability AS pr
ON (CanLoc.type = 3 AND CanLoc.postal_id = pr.id)
LEFT JOIN candidate
ON CanLoc.candid=candidate.CandID
Also, I am using COALESCE() instead of nested IF() blocks. The first non-null value is returned, so since your joins are based on the type 1, 2 or 3, only that row will have a value to qualify.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments