How would I construct this query

danep wrote:
> On Feb 9, 12:27 pm, "danep" <danepow...@gmail.com> wrote:
>> On Feb 9, 11:38 am, "Paul Lautman" <paul.laut...@btinternet.com>
>> wrote:
>>
>>
>>
>>> danep wrote:
>>>> On Feb 9, 10:53 am, Rik <luiheidsgoe...@hotmail.com> wrote:
>>>>> On Fri, 09 Feb 2007 17:22:47 +0100, danep <danepow...@gmail.com>
>>>>> wrote:
>>>>>> I want to find gear that is not reserved for any part of that
>>>>>> period, including the endpoints. In other words, the piece of
>>>>>> gear must be completely available for the entire time period
>>>>>> given, including the endpoints. I would be very grateful (not
>>>>>> to mention impressed!) if you could help me out, thanks!
>>
>>>>> Untested:
>>
>>>>> SELECT g.`gearid`
>>>>> FROM `gear` g
>>>>> WHERE g.`gear`id` NOT IN (
>>>>> SELECT DISTINCT r.`gearid`
>>>>> FROM `reservations` r
>>>>> WHERE
>>>>> (r.`checkoutdate` BETWEEN 'your_start_date' AND
>>>>> 'you_end_date') OR
>>>>> (r.`returndate` BETWEEN 'your_start_date' AND
>>>>> 'you_end_date') )
>>
>>>>> --
>>>>> Rik Wasmus
>>
>>>> That solves half of it, namely how to correlate the gear table to
>>>> the reservations table.... however what if gear is already
>>>> reserved for ALL of the time period you are interested in, and
>>>> then some... Then the "checkoutdate" would fall before your start
>>>> date and the "returndate" would fall after your end date, and
>>>> neither of the between conditions would apply. What if you make
>>>> the WHERE clause
>>
>>>> WHERE g.`gear`id` NOT IN (
>>>> SELECT DISTINCT r.`gearid`
>>>> FROM `reservations` r
>>>> WHERE
>>>> (r.`checkoutdate` BETWEEN 'your_start_date' AND 'you_end_date')
>>>> OR
>>>> (r.`returndate` BETWEEN 'your_start_date' AND 'you_end_date')
>>>> OR
>>>> ('your_start_date' BETWEEN r.`checkoutdate` AND r.`returndate`)
>>>> OR
>>>> ('your_end_date' BETWEEN r.`checkoutdate` AND r.`returndate`)
>>
>>>> That *seems* like it should work, doesn't it? Thanks for your
>>>> help!
>>
>>> Rik's query is rather wrong and way more complicated than it needs
>>> to be I'm afraid, as I guess you found out.
>>
>>> It is far simpler than that.
>>
>>> SELECT g.*
>>> FROM gear g
>>> LEFT JOIN reservations r ON r.gearid = g.gearid
>>> AND r.checkoutdate <= 'periodend'
>>> AND r.returndate >= 'periodstart'
>>> WHERE r.gearid IS NULL
>>
>> That seems very close; however what if the gear has TWO existing
>> reservations, one of which conflicts with the desired time period and
>> one of which doesn't. Then (as far as I can tell) this query would
>> still return the gear as available. Does that make sense?
>
> Whoops, I take it back... I'm having a very difficult time sorting
> through this in my head, but on second thought I think this query
> should work after all. I guess the way to find out would just be to
> try it :)

Yep, I promise you, I've used this sort of query many times.
It LOOKS too simple to do the job, but in fact it is all that is needed!