is this post processing the best way?

[bazz]

Hi, still munching my way through this join query and think I have got it.

I just wonder if to remove unwanted data, the post-processing part is the most efficient way to achieve it.


("SELECT
RD.room_id,
RD.room_name,
RD.room_number,
group_concat(
concat_ws(
'/',
(RB.booking_id),
(RB.room_status),
(RB.check_in_date),
(RB.check_out_date)
) ORDER BY RB.check_in_date ASC
) as booking_data,
RD.business_id
FROM
tbl_rooms_details RD
left outer join tbl_rooms_booked RB
ON RD.business_id = RB.business_id
AND RD.room_id = RB.room_id
AND RD.business_id = ?
AND RB.check_in_date <= ?
AND RB.check_out_date >= ?

GROUP BY RD.room_id
ORDER BY RD.room_number
") or die "prepare statement failed: $DBI::errstr\n";

$sth->execute($business_id, $last_date, $first_date);

my $room;

while ( $room = $sth->fetchrow_hashref)
{
#my $room_id = $room->{room_id};
$room_number = $room->{room_number};
$room_name = $room->{room_name};
my $booking_data = $room->{booking_data};
my $stored_business_id = $room->{business_id};
my @booking_data = (split /,/, $booking_data);
if ($stored_business_id != $business_id)
{
next;
}





bazz

[nkrgupta]

I guess a major part of your while loop comes after

if ($stored_business_id != $business_id)
{
next;
}


(asking since its not included in your code)

However, I suppose the unwanted data can be excluded at the query level itself, but not knowing your business requirements and the db/table structure, I'm not in a position to comment how.

If you're still open on this issue, perhaps you can post complete code, which can then be analysed by someone here. Not that I am a DB expert, but it doesn't take much to give it a shot ;-)

Naveen