dlofrodloh dlofrodloh - 6 months ago 24
MySQL Question

Using aliases in distance calculation

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

Answer

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.