The query must not include people whose postcode is not within the given distance.
SELECT people.* , (((acos(sin((51.51777*pi()/180)) * sin((latitude*pi()/180))+cos((51.51777*pi()/180)) * cos((latitude*pi()/180)) * cos(((-0.079501- longitude)*pi()/180))))*180/pi())*60*1.1515) AS distance FROM people WHERE people.archived = '0' AND distance <= '5' ORDER BY distance DESC, people.name ASC, people.surname ASC LIMIT 0, 50
I’m getting the following error:
Unknown column ‘distance’ in ‘where clause’
However, if I remove distance <= ‘5’ from the WHERE clause, it works and furthermore, in my php looping through the rows, ‘distance’ is clearly being defined as I’m able to print the distance.
select *
from (select people.*,
(((ACOS(SIN((51.51777*PI()/180)) *
SIN((latitude*PI()/180))+COS((51.51777*PI()/180)) *
COS((latitude*PI()/180)) * COS(((-0.079501- longitude)*
PI()/180))))*180/PI())*60*1.1515) as distance
from people
where archived = 0) dt
where distance <= 5
order by distance desc,
name asc,
surname asc
limit 0, 50
AFAIK you cant use aliases in the order by clause.
They can be used in an order by clause, not in a where clause.
select * from (SELECT people.* , (((acos(sin(('52.031130458238'*pi()/180)) * sin((latitude*pi()/180))+cos(('52.031130458238'*pi()/180)) * cos((latitude*pi()/180)) * cos((('0.75861321496378' - longitude)*pi()/180))))*180/pi())*60*1.1515) AS distance FROM people IF( people.postcode != '', LEFT JOIN ordnance_survey_locations ON people.postcode = ordnance_survey_locations.postcode , LEFT JOIN counties ON people.county = counties.id ) WHERE people.archived = '0' ) dt WHERE distance <= 60 ORDER BY distance ASC, name ASC, surname ASC LIMIT 0, 50
The above is my failed attempt at joining the ordnance survey table of postcodes if postcode != ‘’, or the counties table if postcode = ‘’.
I’ve also tried a CASE statement but to no avail.
For each county I have a central longitude/latitude - so it’s a rough backup plan in the absence of a postcode.
I’ve just realised that there’s no such thing as a conditional left join because both joins need to happen. So the real question is, how do I determine where the longitude/latitude info is coming from?
select * from (SELECT candidates.* , (((acos(sin(('57.148265504629'*pi()/180)) * sin((IF(candidates.postcode != '',latitude,c_latitude)*pi()/180))+cos(('57.148265504629'*pi()/180)) * cos((IF(candidates.postcode != '',latitude,c_latitude)*pi()/180)) * cos((('-2.0930410110683' - IF(candidates.postcode != '',longitude,c_longitude))*pi()/180))))*180/pi())*60*1.1515) AS distance FROM candidates LEFT JOIN ordnance_survey_locations ON candidates.postcode = ordnance_survey_locations.postcode LEFT JOIN counties ON candidates.county = counties.id WHERE candidates.archived = '0' ) dt WHERE distance <= 40 ORDER BY distance ASC, name ASC, surname ASC LIMIT 0, 50
in future threads, would you kindly do us a really big favour and please consider ~not~ posting your code all on one single humoungously long line, because although computers have no problem with it, human beings find scrolling and scrolling and scrolling a complex sql statement to be a [size=7]huge[/size] pain in the @ss
I will do - it’s the first time that I’ve used the ‘select syntax’ feature and all of my previous use of ‘quote formatting’ buttons on forums have done the wrapping automatically.
New problem: Unknown column ‘people.contract_type’ in ‘order clause’
I’ve prefixed the ‘archived’ column with ‘people.’ but it doesn’t complain about that. I did a test where I replaced people.archived != ‘9’ with people.contract_type = ‘1’ - it worked fine. As soon as I introduced a second condition in WHERE, it failed.
SELECT *
FROM (SELECT people.* ,
(((acos(sin(('51.517626393425'*pi()/180)) *
sin((IF(people.postcode != '',latitude,c_latitude)*pi()/180))+cos(('51.517626393425'*pi()/180)) *
cos((IF(people.postcode != '',latitude,c_latitude)*pi()/180)) *
cos((('-0.073421625736112' - IF(people.postcode != '',longitude,c_longitude))*pi()/180))))*180/pi())*60*1.1515)
AS distance
FROM people
LEFT JOIN ordnance_survey_locations ON people.postcode = ordnance_survey_locations.postcode
LEFT JOIN counties ON people.county = counties.id
WHERE people.archived != '9' AND people.contract_type = '2') dt
WHERE distance <= 20
ORDER BY distance ASC, people.contract_type ASC, name ASC, surname ASC
LIMIT 0, 50
it’s actually quite simple – the outer query can’t see the people table, it can only see the “dt” table, the derived table created by the subquery in the FROM clause
where exactly is that first reference, in the outer query or inside the subquery?
maybe it would help if we slightly reformatted the query…
SELECT *
FROM ( [COLOR="Blue"]SELECT people.*
, (((ACOS(SIN ... ))) AS distance
FROM people
LEFT
JOIN ordnance_survey_locations
ON ordnance_survey_locations.postcode = people.postcode
LEFT
JOIN counties
ON counties.id = people.county
WHERE people.archived != '9'
AND people.contract_type = '2'[/COLOR]) AS dt
WHERE distance <= 20
ORDER
BY distance ASC
, [COLOR="Red"]people[/COLOR].contract_type ASC
, name ASC
, surname ASC
LIMIT 0,50
the outer query is not aware of any table names used in the subquery