Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: Using multiple ANDs in a statement

    Help!

    If I had a table:

    Room Date Availability
    1 01/02/2004 YES
    1 02/02/2004 NO
    1 03/02/2004 YES
    1 04/02/2004 YES

    If I wanted to find out if it was true that Room 1 was available between 01/02/2004 and 03/02/2004 and would only return true if all dates are available. How can I reduce the statement:

    WHERE (Date=01/02/2004 AND Availability=YES)
    AND (Date=02/02/2004 AND Availability=YES)
    AND (Date=03/02/2004 AND Availability=YES)

    If I used BETWEEN 01/02/2004 AND 03/02/2004 it would return 01/02/2004 and 03/02/2004 instead of checking that they are all available.

    How can I make sure all dates in the between statement are available and only return true if they are.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    time intervals and durations are notoriously difficult to handle in sql

    what you want is to know that there is an "availability" on each day in the range

    you could do it with an EXISTS predicate on each date within the interval, but this means hardcoding them all, and it could get repetitive and tedious:
    Code:
    select 'yes' as roomisfree
      from timetable
     where room = 1
       and datefld = '2004-02-01'   -- hardcode first date
       and availability = 'yes'
       and exists
           ( select 1 
               from timetable
              where room = 1
                and datefld = '2004-02-02'   -- hardcode second date
                and availability = 'yes'
           )
       and exists
           ( select 1 
               from timetable
              where room = 1
                and datefld = '2004-02-03'   -- hardcode third date
                and availability = 'yes'
           )
    note that if all the conditions are met, you will get back one row with one column called roomisfree, otherwise you will get 0 rows back

    the same results can be obtained like this:
    Code:
    select availability as roomisfree
      from timetable
     where room = 1
       and datefld between '2004-02-01'   -- hardcode first date
                       and '2004-02-03'   -- hardcode last date
       and availability = 'yes'
    group
        by availability
    having count(*) = 3      -- number of dates
    here you need code only the first and last dates in the interval, as well as the number of dates the range covers
    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
  •