dlofrodloh - 8 months ago 46

MySQL Question

I've got 2 tables: candidate and

`candidate_location`

`candidate_location`

`type`

`lat`

`lng`

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.