Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,129
    Thanks
    300
    Thanked 12 Times in 12 Posts

    Exclamation query not returning the expected results

    here is my query
    Code:
    SELECT *
    FROM tbl_accommodation AS a
    LEFT JOIN tbl_accommodation_types AS at ON at.accommodationID = a.accommodationID
    LEFT JOIN tbl_type AS t ON t.typeID = at.typeID
    LEFT JOIN tbl_accommodation_location AS loc ON loc.accommodationID = a.accommodationID
    LEFT JOIN tbl_locations AS l ON l.locationID = loc.locationID
    WHERE (
    DEGREES( ACOS( SIN( RADIANS( 50.07000 ) ) * SIN( RADIANS( l.latitude ) ) + COS( RADIANS( 50.07000 ) ) * COS( RADIANS( l.latitude ) ) * COS( RADIANS( - 5.69500 - l.longitude ) ) ) ) *60 * 1.1515
    ) <=10
    AND t.typeID =1
    OR t.typeID =2
    OR t.typeID =3
    OR t.typeID =4
    OR t.typeID =5
    AND a.petFriendly =1
    AND a.parking =1
    AND a.smokingPermitted =1
    AND a.wheelchairAdapted =1
    AND a.wifi =1
    AND a.swimmingPool =1
    AND a.airCon =1
    AND a.woodBurner =1
    AND a.centralHeating =1
    AND a.hottub =1
    AND a.garden =1
    AND a.bbq =1
    AND a.cot =1
    AND a.highchair =1
    AND a.pricePN >=0
    AND a.pricePN <=110
    ORDER BY createdDate DESC
    LIMIT 0 , 10
    the problem is its returning records where not all of these
    Code:
    AND a.petFriendly =1
    AND a.parking =1
    AND a.smokingPermitted =1
    AND a.wheelchairAdapted =1
    AND a.wifi =1
    AND a.swimmingPool =1
    AND a.airCon =1
    AND a.woodBurner =1
    AND a.centralHeating =1
    AND a.hottub =1
    AND a.garden =1
    AND a.bbq =1
    AND a.cot =1
    AND a.highchair =1
    are actually 1. can someone please explain what i am doing wrong?

    thanks
    Luke

  2. #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,878
    Thanks
    3
    Thanked 455 Times in 445 Posts
    The query returns any rows with a TRUE value for the WHERE clause. Because you are OR'ing values, you are getting all the rows with t.typeID =2, 3, 4, or 5 regardless of the other conditions. You need to put () around the OR terms to make them one expression.

    Also, it is cleaner and easier to build a multi-value OR term using the IN() comparison -

    t.typeID IN(1,2,3,4,5)

    Which actually would eliminate the need to put () around the OR terms because it is already one expression.
    Finding out HOW to do something is called research, i.e. keep searching until you find the answer. After you attempt to do something and cannot solve a problem with it yourself, would be when you ask others for help.

  3. Users who have thanked CFMaBiSmAd for this post:

    LJackson (12-24-2013)

  4. #3
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,129
    Thanks
    300
    Thanked 12 Times in 12 Posts
    Thanks Mate!!!


 

Posting Permissions

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