My Query works and returns the correct results. It's generated on the fly depending on the search criteria that people enter. However, this search can take 15 seconds to run depending on the slack days they enter. The main slowdown is dependant on the dates you enter. If you're looking for a property that's available between dates and you want a 3 day slack. So it'll also check that date and + 3 days or - 3 days.

Here is my structure:

Code:
CREATE TABLE IF NOT EXISTS `tbl_events` (
  `fld_event_id` int(11) NOT NULL auto_increment,
  `fld_event_title` varchar(100) NOT NULL default '',
  `fld_event_price` decimal(10,2) NOT NULL,
  `fld_event_start_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `fld_event_end_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `fld_event_type` varchar(150) default '0',
  `fld_event_created_on` datetime default NULL,
  `fld_event_modified_on` datetime default NULL,
  `Advert_ID` int(10) NOT NULL,
  `Enquiry_ID` int(10) NOT NULL,
  PRIMARY KEY  (`fld_event_id`),
  UNIQUE KEY `match_id` (`fld_event_id`,`Advert_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=794 ;
Code:
SELECT *, advert.Advert_ID as Advert_ID FROM advert, advert_category
WHERE Advert_Active = 'Yes'
AND advert_category.Advert_ID = advert.Advert_ID
AND ((advert.Advert_ID IN (select Advert_ID from tbl_events
WHERE fld_event_start_date <= "2011-12-06 00:00:00 00:00:00"
 AND fld_event_end_date >= "2011-12-13 00:00:00 00:00:00"
AND fld_event_type = "Available"))) OR ((advert.Advert_ID IN (select Advert_ID from tbl_events
 WHERE fld_event_start_date <= "2011-12-07 00:00:00 00:00:00"
AND fld_event_end_date >= "2011-12-14 00:00:00 00:00:00"
AND fld_event_type = "Available"))) OR ((advert.Advert_ID IN (select Advert_ID from tbl_events
WHERE fld_event_start_date <= "2011-12-05 00:00:00 00:00:00"
AND fld_event_end_date >= "2011-12-12 00:00:00 00:00:00"
AND fld_event_type = "Available"))) OR ((advert.Advert_ID IN (select Advert_ID from tbl_events
 WHERE fld_event_start_date <= "2011-12-04 00:00:00 00:00:00"
AND fld_event_end_date >= "2011-12-11 00:00:00 00:00:00"
AND fld_event_type = "Available")))
GROUP BY advert.Advert_ID
ORDER By Advert_Bedrooms asc, Advert_Sleeps asc