SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2008
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL variable can't be used in sort

    Hi all,

    The query must not include people whose postcode is not within the given distance.

    Code MySQL:
    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
    spitfireweb

  2. #2
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    AFAIK you cant use aliases in the order by clause.
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  3. #3
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    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.

  4. #4
    SitePoint Enthusiast
    Join Date
    Nov 2008
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much swampBoogie.
    spitfireweb

  5. #5
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by swampBoogie View Post
    They can be used in an order by clause, not in a where clause.
    lol knew it was somewhere in there!
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  6. #6
    SitePoint Enthusiast
    Join Date
    Nov 2008
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A new level of complication...

    Code MySQL:
    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

  7. #7
    SitePoint Enthusiast
    Join Date
    Nov 2008
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    SitePoint Enthusiast
    Join Date
    Nov 2008
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Success.

    Code MySQL:
    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
    spitfireweb

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    dear spitfireweb

    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast
    Join Date
    Nov 2008
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    dear spitfireweb

    Code:
    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.
    spitfireweb

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you should really format your sql properly in your application code, not just when you post it in forums

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Enthusiast
    Join Date
    Nov 2008
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code MySQL:
    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
    spitfireweb

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Enthusiast
    Join Date
    Nov 2008
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    spitfireweb

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by spitfireweb View Post
    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...
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •