Selecting no records if condition is true

Discussion in 'ASP General' started by Mark, Jan 29, 2004.

  1. Mark

    Mark Guest

    Hi - I have a numebr of rooms, which I am making available.

    table: single - has single_id and single_name
    rental_single has rental_id, single_id, check_in and check_out

    They are linked by single_id.

    I want to query so that when given two dates, it will return the
    single_id that is free between two dates, which means I need to exclude
    a singe_id if it has a linked entrey in the rental_single table, which
    has a check_in and check_out date which iverlap with my form entries.

    My query so far is:

    SELECT DISTINCT single.single_id
    FROM single INNER JOIN rental_single ON single.single_id =
    rental_single.single_id
    WHERE ((NOT ((rental_single.check_in) BETWEEN #1/1/2004# AND
    #20/2/2004#))
    AND
    (NOT ((rental_single.check_out) BETWEEN #1/1/2004# AND #20/1/2004#)));


    Trouble is, if I have an entry in the rental_single table with a check
    in of #12/12/2004# and a check_out of #14/12/2004# and a checkin of
    #2/1/2004# and #5/2/2004# it will still show that room linked as being
    available - I want to exclude the single_id altogether, if ANY of the
    entries in thje linked rental_single room fall within the two dates I am
    looking for.

    I'd really appreciate any pointers,

    Thanks,



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Mark, Jan 29, 2004
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. lj
    Replies:
    0
    Views:
    555
  2. -
    Replies:
    12
    Views:
    697
    Remon van Vliet
    Jun 15, 2005
  3. Replies:
    3
    Views:
    433
  4. bdb112
    Replies:
    45
    Views:
    1,350
    jazbees
    Apr 29, 2009
  5. Bill W.
    Replies:
    13
    Views:
    296
    Phillip Gawlowski
    May 9, 2011
Loading...

Share This Page