bazz
05-05-2008, 12:21 AM
Hi,
I am querying two tables 1) to get the room_ids and 2) to see which dates a room_id is not booked.
As it is, the query outputs no error and no results which leaves me very :confused:
my $sth = $bookings_db_connect->prepare ("SELECT RD.room_id, RD.room_name_or_number, RD.description, RD.max_occupancy, RD.rate
FROM tbl_rooms_details RD
WHERE room_id NOT IN (
SELECT RB.room_id
FROM tbl_rooms_booked RB
#WHERE check_in_date >= ?
#AND check_out_date <= ?
)
") or die "prepare statement failed: $DBI::errstr\n";
I thought that that should get me all details in tbl_rooms_booked because I had commented out the dates
my second attempt fared no better :(
my $sth = $bookings_db_connect->prepare
("SELECT
RD.room_id,
RD.room_name_or_number,
RD.description,
RD.max_occupancy,
RD.rate
FROM tbl_rooms_details RD
LEFT JOIN tbl_rooms_booked RB
ON (
RD.room_id = RB.room_id
AND check_in_date >= ?
AND check_out_date <= ?
)
WHERE max_occupancy >= '0'
GROUP BY
RD.room_id,
RD.room_name_or_number,
RD.description,
RD.max_occupancy,
RD.rate
HAVING count(*) = 0
ORDER BY
RD.max_occupancy,
RD.rate,
RD.description
") or die "prepare statement failed: $DBI::errstr\n";
$sth->execute($check_in_date, $check_out_date);
FYI, The dates are formatted correctly
I am totally confused in trying to find what isn't in the table_rooms_booked so any help you can offer will be appreciated.
bazz
I am querying two tables 1) to get the room_ids and 2) to see which dates a room_id is not booked.
As it is, the query outputs no error and no results which leaves me very :confused:
my $sth = $bookings_db_connect->prepare ("SELECT RD.room_id, RD.room_name_or_number, RD.description, RD.max_occupancy, RD.rate
FROM tbl_rooms_details RD
WHERE room_id NOT IN (
SELECT RB.room_id
FROM tbl_rooms_booked RB
#WHERE check_in_date >= ?
#AND check_out_date <= ?
)
") or die "prepare statement failed: $DBI::errstr\n";
I thought that that should get me all details in tbl_rooms_booked because I had commented out the dates
my second attempt fared no better :(
my $sth = $bookings_db_connect->prepare
("SELECT
RD.room_id,
RD.room_name_or_number,
RD.description,
RD.max_occupancy,
RD.rate
FROM tbl_rooms_details RD
LEFT JOIN tbl_rooms_booked RB
ON (
RD.room_id = RB.room_id
AND check_in_date >= ?
AND check_out_date <= ?
)
WHERE max_occupancy >= '0'
GROUP BY
RD.room_id,
RD.room_name_or_number,
RD.description,
RD.max_occupancy,
RD.rate
HAVING count(*) = 0
ORDER BY
RD.max_occupancy,
RD.rate,
RD.description
") or die "prepare statement failed: $DBI::errstr\n";
$sth->execute($check_in_date, $check_out_date);
FYI, The dates are formatted correctly
I am totally confused in trying to find what isn't in the table_rooms_booked so any help you can offer will be appreciated.
bazz