MySQL variable can't be used in sort

[spitfireweb]

Hi all,

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.

Thanks in advance,

Ben

[spikeZ]

AFAIK you cant use aliases in the order by clause.

[swampBoogie]

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.

[spitfireweb]

Thank you very much swampBoogie.

[spikeZ]

They can be used in an order by clause, not in a where clause.

lol knew it was somewhere in there!

[spitfireweb]

A new level of complication...

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'd appreciate any help you could give.

Thanks,

[spitfireweb]

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?

[spitfireweb]

Success.

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

[r937]

dear spitfireweb

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 huge pain in the @ss

thank you

[spitfireweb]

dear spitfireweb

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 huge pain in the @ss

thank you

r937,

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.

[r937]

you should really format your sql properly in your application code, not just when you post it in forums

[spitfireweb]

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

Any help would be appreciated.

Cheers,

Ben

[r937]

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

[spitfireweb]

That makes sense but, why does it not fail during the first reference to a column within the people table? Is this just a MySQL quirk?

Thanks.

[r937]

That makes sense but, why does it not fail during the first reference to a column within the people table?

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 ( 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') AS dt
 WHERE distance <= 20
ORDER 
    BY distance ASC
     , people.contract_type ASC
     , name ASC
     , surname ASC
LIMIT 0,50

the outer query is not aware of any table names used in the subquery