Oh...it becomes a bit clearer with some indenting changes:
SELECT d.name, d.houseNumber, d.postalCode, d.city, d.street, d.latitude, d.longitude, d.distance, d.BusinessID, d.BusinessType, b.Description
FROM (
SELECT
z.name,
z.houseNumber,
z.postalCode,
z.city,
z.street,
z.BusinessID,
z.BusinessType,
z.latitude, z.longitude,
p.radius,
p.distance_unit
* DEGREES(ACOS(COS(RADIANS(p.latpoint))
* COS(RADIANS(z.latitude))
* COS(RADIANS(p.longpoint - z.longitude))
+ SIN(RADIANS(p.latpoint))
* SIN(RADIANS(z.latitude)))) AS distance
FROM Businesses AS z
JOIN (
SELECT 56.11164290 AS latpoint, -3.15779220 AS longpoint, 30.0 AS radius, 111.045 AS distance_unit
) AS p ON 1=1
INNER JOIN BusinessTypes AS b ON z.BusinessType = b.BusinessType
WHERE z.latitude BETWEEN p.latpoint - (p.radius / p.distance_unit) AND p.latpoint + (p.radius / p.distance_unit)
AND z.longitude BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
) AS d
WHERE distance <= radius AND b.Description = 'Coffee'
ORDER BY distance
The initial SELECT
line only knows about the sub-query that you've aliased as 'd'
, so b.Description
is not available there. You could add it to the sub-query's field list, then select it as d.Description
. Likewise, I suspect you'll have to move the b.Description = 'Coffee'
into the sub-query's WHERE
clause, for the same availability issue.