Selecting no records if condition is true

[Mark]

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_i d
FROM single INNER JOIN rental_single ON single.single_i d =
rental_single.s ingle_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!

[Curt_C [MVP]]

It's not really an ASP question but a SQL one....
But

Select * from single where single_id not in
(Select single_id from rental where date......)

try that route...

--
Curt Christianson
Owner/Lead Developer, DF-Software
www.Darkfalz.com
"Mark" <an*******@devd ex.com> wrote in message
news:u$******** ******@TK2MSFTN GP09.phx.gbl...

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_i d
FROM single INNER JOIN rental_single ON single.single_i d =
rental_single.s ingle_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!