I have myself a bit mixed up with this query I put it in pastebin.
Unknown column 'b.Description' in 'field list'
So does the BusinessTypes table have a column named Description (case-sensitive)?
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.
SELECT d.name, d.houseNumber, d.postalCode, d.city, d.street, d.latitude, d.longitude, d.distance, d.BusinessID, BusinessType
FROM (
SELECT
b.Description,
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
INNER JOIN BusinessTypes AS b
ON z.BusinessType = b.BusinessType
WHERE b.BusinessType = 'Coffee'
JOIN (
SELECT 56.11164290 AS latpoint, -3.15779220 AS longpoint,
30.0 AS radius, 111.045 AS distance_unit
) AS p ON 1=1
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
ORDER BY distance
[/code}
I tried that Here's what I'm thinking:
SELECT
d.name,
d.houseNumber,
d.postalCode,
d.city,
d.street,
d.latitude,
d.longitude,
d.distance,
d.BusinessID,
d.BusinessType,
d.Description -- just another field from the sub-query now
FROM (
SELECT
z.name,
z.houseNumber,
z.postalCode,
z.city,
z.street,
z.BusinessID,
z.BusinessType,
b.Description, -- use the sub-query's join to get this
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
b.Description = 'Coffee' -- test for this in sub-query
AND 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
ORDER BY distance