Help with a query please

From: john <nospam_at_nospam.com>
Date: Tue, 22 Apr 2003 23:03:19 +0100
Message-ID: <Y1jpa.11953$9C6.450614_at_wards.force9.net>

Hi,

I have a table in my MySQL database called bookings:

 CREATE TABLE WMS_Bookings (
   Booking_ID int(11) DEFAULT '' NOT NULL auto_increment,    User_ID int(11) DEFAULT '0' NOT NULL ,    Project_ID int(11) DEFAULT '0' NOT NULL ,    Rep_ID int(11) DEFAULT '0' NOT NULL ,    PCT_ID int(11) DEFAULT '0' NOT NULL ,    Practice_ID int(11) DEFAULT '0' NOT NULL ,

   Booking_Start_Date date  DEFAULT '0000-00-00' NOT NULL ,
   Booking_End_Date date  DEFAULT '0000-00-00' NOT NULL ,
   Booking_Days int(11)  DEFAULT '1' NOT NULL ,
   Booking_Hours decimal(3,2)  DEFAULT '0.00' NOT NULL ,
   Booking_Mileage int(5)    ,
   Booking_Status int(2)  DEFAULT '0' NOT NULL ,

   Work_Type varchar(20) DEFAULT '' NOT NULL ,    Additional_Notes text ,
   PRIMARY KEY (Booking_ID,User_ID,Project_ID,Rep_ID,PCT_ID)  );

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

Thanks for your help Received on Wed Apr 23 2003 - 00:03:19 CEST