Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2005
    Posts
    11

    Unanswered: Select problem - I am a newbie

    Hi guys wonder if you can help. I am writing a query to return search results. The trouble is some of the critera returns invalid results. I think I need to put some () around the statement, but dont know where can someone help?

    SELECT * FROM tblmilkers A
    INNER JOIN tbldates B ON A.ID = B.milkerID
    WHERE B.date BETWEEN '2009-05-17 00:00:00' AND '2009-08-30 23:59:59'
    AND A.nearestTown = 143 OR A.otherWorkableTowns LIKE '|143|'
    AND B.status = 1 AND shedPreference = 'Rotary' AND skillSet = 'Sole Charge or Assistant' AND milkingExperience = '0-1 years' AND age = '18 and under' ORDER BY B.date, A.ratePerMilking ASC

  2. #2
    Join Date
    Mar 2005
    Posts
    11

    Sorted it

    Used a sub select

  3. #3
    Join Date
    Mar 2005
    Posts
    11

    Nope still not working

    I take it back, any suggestions would be appreciated, I am tearing my hair out with this one.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is your WHERE clause --
    Code:
    WHERE B.date BETWEEN '2009-05-17 00:00:00' 
                     AND '2009-08-30 23:59:59'    -- this is c1
      AND A.nearestTown = 143                     -- this is c2
       OR A.otherWorkableTowns LIKE '|143|'       -- this is c3    
      AND B.status = 1                            -- this is c4
      AND shedPreference = 'Rotary'               -- this is c5
      AND skillSet = 'Sole Charge or Assistant'   -- this is c6
      AND milkingExperience = '0-1 years'         -- this is c7 
      AND age = '18 and under'                    -- this is c8
    so this is equivalent to --
    Code:
    WHERE c1 AND c2 OR c3 AND c4 AND c5 AND c6 AND c7 AND c8
    okay, now, ANDs take precedence over ORs, so this is evaluated as follows --
    Code:
    WHERE ( c1 AND c2 ) 
       OR ( c3 AND c4 AND c5 AND c6 AND c7 AND c8)
    however, i'd be willing to bet that what you want is this --
    Code:
    WHERE c1 AND ( c2 OR c3 ) AND c4 AND c5 AND c6 AND c7 AND c8
    tip: when mixing ANDs and ORs, always code your own parentheses to get the logical evaluation you need

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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