SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2002
    Location
    London
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem using 'IF' statement in 'WHERE' clause

    I'm a bit of a novice at SQL so I was hoping someone could take a look at the example query below and let me know why it's hanging / taking forever to execute?

    Code MySQL:
    SELECT DISTINCT(pr.id), IFNULL(pr.newLongitude,os.Longitude) as lng, IFNULL(pr.newLatitude,os.Latitude) as lat FROM properties as pr use index (xyindex), postcode_to_os as os WHERE pr.Office_ID='1234' AND pr.Customer_ID='4321' AND pr.Active='on' AND (IF(pr.X_Coord=0 AND pr.Y_Coord=0, TRUE, pr.X_Coord=os.X_Coord AND pr.Y_Coord=os.Y_Coord))

    For the most part the rows of properties in my properties table have X_Coord and Y_Coord values which match up to X_Coord and Y_Coord values in the postcode_to_os table. However, in some cases there's no match and the value of this field is just equal to 0. In the sql above I basically still want these pr.X_Coord=0 properties so I thought the IF statement would ignore the

    Code MySQL:
    pr.X_Coord=os.X_Coord AND pr.Y_Coord=os.Y_Coord

    bit in these cases.

    Thanks for any advice.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT pr.id
         , COALESCE(pr.newLongitude,os.Longitude) AS lng
         , COALESCE(pr.newLatitude,os.Latitude) AS lat 
      FROM properties AS pr USE index (xyindex)
    LEFT OUTER
      JOIN postcode_to_os AS os 
        ON os.X_Coord = pr.X_Coord 
       AND os.Y_Coord = pr.Y_Coord
     WHERE pr.Office_ID='1234' 
       AND pr.Customer_ID='4321' 
       AND pr.Active='on'
    note that DISTINCT is not a function

    please try not to write your SQL on one humungous long single line
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2002
    Location
    London
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937

    Thanks for the post but your modifications are not achieving what I'm after. If pr.X_Coord equals 0 or pr.Y_Coord equals 0 then I want the resulting lat and lng values to equal 0 or null or '' - something that I can use to know the co-ordinates are not available.

    I probably did not explain this very well in my original post.

    Also, how can I get DISTINCT to work in this sql query as I only want one unique row for every pr.id?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if pr.X_Coord equals 0 or pr.Y_Coord equals 0, then the join won't work (assuming there are no postcode locations with a 0 coordinate), so you get the pr row without a matching os row, which is how a LEFT OUTER JOIN works

    therefore all you have to do is display the pr values, since they're 0
    Code:
    SELECT pr.id
         , pr.newLongitude AS lng
         , pr.newLatitude AS lat 
      FROM properties AS pr USE index (xyindex)
    LEFT OUTER
      JOIN postcode_to_os AS os 
        ON os.X_Coord = pr.X_Coord 
       AND os.Y_Coord = pr.Y_Coord
     WHERE pr.Office_ID='1234' 
       AND pr.Customer_ID='4321' 
       AND pr.Active='on'
    as for one row per pr.id, assuming each pr.id can have multiple os rows, how do you want to choose which one?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2002
    Location
    London
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, I take it back - this logic does seem to achieve what I'm after.

    Thanks very much :-)


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
  •