Re: Help with a query please

"Shaun" <shaun@mania.plus.com> wrote in message news:20030422220705.6222.qmail@pb1.pair.com... > [snip] > How can i check whether a user is already booked for a given period? I > have tried the following query but this only checks for a booking that > starts before the gaiven date and ends after it > > SELECT B.* > FROM WMS_User U, WMS_Bookings B > WHERE B.User_ID = '1' > AND B.Booking_Start_Date >= '2003-04-28' > AND B.Booking_End_Date <= '2003-04-29' > > if a user is booked from 2003-04-29 to 2003-05-01 then the query returns no > rows... So what you want to know is if there is any overlap between an input period and one or more already- booked periods? Using an existing booked period as reference, any arbitrary date can be _before, _in, or _after. Looking at the start and end dates of any arbitrary period, there are nine possible combinations (start=caps, end=lower): Bb Bi Ba Ib Ii Ia Ab Ai Aa If we constrain the end date to be on or after the start date, three combinations are impossible Bb Bi Ba __ Ii Ia __ __ Aa Now, you can test for periods which DO ** Bi Ba __ Ii Ia __ __ ** or DO NOT Bb ** ** __ ** ** __ __ Aa coincide; it looks simpler to test for non-coincidence and assume the opposite if it fails. SELECT b.* FROM WMS_User u JOIN WMS_Bookings b ON u.ID = b.User_ID WHERE u.ID = $user AND NOT ( $end_date < b.Booking_Start_Date OR $start_date > b.Booking_End_Date ) This should return all bookings which collide with your input period. Hope this helps...