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. Advertisements

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. Zenobia
    Replies:
    0
    Views:
    387
    Zenobia
    Jul 8, 2004
  2. lj
    Replies:
    0
    Views:
    651
  3. -
    Replies:
    12
    Views:
    975
    Remon van Vliet
    Jun 15, 2005
  4. Remi Villatel

    Loop until condition is true

    Remi Villatel, Jun 18, 2005, in forum: Python
    Replies:
    40
    Views:
    1,370
    Mike Meyer
    Jun 23, 2005
  5. Replies:
    3
    Views:
    346
    =?ISO-8859-1?Q?Erik_Wikstr=F6m?=
    Dec 31, 2006
  6. Replies:
    3
    Views:
    511
  7. bdb112
    Replies:
    45
    Views:
    1,940
    jazbees
    Apr 29, 2009
  8. Bill W.
    Replies:
    13
    Views:
    556
    Phillip Gawlowski
    May 9, 2011
Loading...