Combining multiple rows to make up a date range

I'm not sure if this is possible with MySQL and its a little complicated, but I'll try and explain as best as possible.

I amending out accommodation availability & price checking system based on an existing database. The visitor will be able to select an arrival and a departure date and I want to be able to show all the rates/offers that are available for those dates. Easy. However, an offer may be set up to be available for only the first part of the given date range and a second rate may be set up for that room type covering the later part of the date range.

e.g. I select September 29th until October 3rd for my stay. A rate has been set up for room 1 to cover September 1st - 30th, and a second rate for October 1st - 31st.

Can I query the table to check if besides rates that span the entire range, there are pairs (or maybe even more) rates for the same room id that together cover the date range, and retrieve the rates & descriptions for those?

The query below checks for basic rates that are valid between the given date开发者_运维问答s (where roomtype is the id of the room):

SELECT id, caption, price, startdate, enddate, nights, roomtype,
(@arrival := DATE('2011-09-29')) AS arrivaldate,
(@departure := DATE('2011-10-05')) AS departuredate
FROM bnbrates
WHERE
bnb_ref = 16639
AND active = 'TRUE'
AND rooms != 0
AND @arrival < enddate
AND @departure > startdate
AND roomtype != ''  
ORDER BY roomtype, enddate, startdate, price;

and this is a sample of the data stored:

id       | caption     | price | startdate     | enddate      | nights | roomtype | arrivaldate | departuredate
-------------------------------------------------------------------------------------------------------------------
23553 | Single      | 50     | 2011-10-01 | 2011-10-31 | 1        | 1064       | 2011-09-29 | 2011-10-05
23544 | Double     | 55     | 2011-09-01 | 2011-09-30 | 1        | 1647       | 2011-09-29 | 2011-10-05
23545 | Double     | 80     | 2011-10-01 | 2011-10-31 | 1        | 1647       | 2011-09-29 | 2011-10-05
30312 | Triple       | 109   | 2011-09-01 | 2011-09-30 | 1        | 1649       | 2011-09-29 | 2011-10-05
34234 | Executive | 109   | 2011-09-01 | 2011-09-30 | 1        | 1653       | 2011-09-29 | 2011-10-05
23569 | Executive | 99     | 2011-10-01 | 2011-10-31 | 1        | 1653       | 2011-09-29 | 2011-10-05

The desired result would for example combine 23544 with 23545 and 34234 with 23569

Thank you in advance for your help, and apologies if this doesn't make sense...