join conditional problem.

[bazz]

Hi,

I am joining three tables (1) to get the room data, (2) to check availability and (3) to get the price.

All is tickety boo except that the price bit is being got wrongly.

I get the rooms that are available to show up but, what I need is for it to give me the price for each room on each night. (the prices may not be the same, 2 nights running.


tbl_room_details
| room_id | room_type | room_name | room_number |

tbl_rooms_booked
| booking_id | ref_id | room_id | check_in_date | check_out_date |

tbl_prices
| price_id | room_id | rate | date_from | date_to |

(tbl_prices - date from/to, is the range of dates when the price is available - think high season/low season).

Currently I get the room to return, everytime there is a date available, with all details and the differing prices.

What I need to get is the room details returned once with the corresponding price for each individual night odf the visit.

Can any of you please help as I think this might be beyond me.


my $sth = $bookings_db_connect->prepare ("SELECT
RD.room_id,
RD.room_type,
RD.room_number,
RD.room_name,
RD.description_short,
RD.max_occupancy,
RD.image_url,
RR.rack_rate_id
FROM tbl_rooms_details RD
INNER JOIN tbl_room_rates RR
WHERE RD.room_id = RR.room_id
AND RD.room_id NOT IN ( SELECT
booking_id
FROM tbl_rooms_booked
WHERE check_in_date <= ?
AND check_out_date >= ? )
AND RR.date_from <= ?
AND RR.date_to >= ?
") or die "prepare statement failed: $DBI::errstr\n";
$sth->execute($departure_date_requested, $arrival_date_requested, $arrival_date_requested, $departure_date_requested );



bazz

[BubikolRamios]

I suppose , first try to avoid "NOT IN", use join instead on room_id on all three tables, then filter with where.

[bazz]

I suppose , first try to avoid "NOT IN", use join instead on room_id on all three tables, then filter with where.

Thanks I'll consider that, however, the problem seems to lie with the last two lines. I don't just want to identify what room is available; I want to get the price for the room, for each proposed night.

I think I may have to get the date_from and date_to from tbl|_room_rates and post process each date of the booking against each room cost on that date.

pondering some more.....

bazz