Hotel availability search query on MYSQL -
this question exact duplicate of:
i having little problem calculating total price of each room hotel.
for example:
room_1_total 170 on each row hotel_id 1. room_2_total 170 on each row hotel_id 1.
room_1_total 10 on each row hotel_id 2.(i m sure, not working) room_2_total 10 on each row hotel_id 2.(for sure, not working)
and on...
here code along output..
http://sqlfiddle.com/#!2/575d3/2
mysql query:
db structure , dummy data...
create table if not exists `omc_hotel` ( `id` int(11) not null auto_increment, `name` varchar(100) not null, primary key (`id`) )engine=innodb default charset=latin1; insert `omc_hotel` (`id`, `name`) values (1, 'hotel abc'), (2, 'hotel csb'), (3, 'hotel csd'), (4, 'hotel ndg'); create table if not exists `omc_hotel_room` ( `id` int(11) not null auto_increment, `name` varchar(100) not null, `pax_min` int(11) default null, `pax_max` int(11) default null, `quantity` int(11) default null, `hotel_id` int(11) not null, primary key (`id`) ) engine=innodb default charset=latin1 ; insert `omc_hotel_room` (`id`, `name`, `pax_min`, `pax_max`, `quantity`, `hotel_id`) values (1,'single',1,1,150,1), (2,'single',1,1,250,2), (3,'double',2,2,200,1), (4,'double',2,2,405,2), (5,'double',1,1,405,3), (6,'double delax',2,2,50,3), (7,'double',1,1,100,4); create table if not exists `omc_hotel_room_extra_quota` ( `id` int(11) not null auto_increment, `hotel_id` int(11) not null, `room_type_id` int(11) not null, `quantity` int(11) not null, `date` date not null, primary key (`id`) ) engine=innodb default charset=utf8; insert `omc_hotel_room_extra_quota` (`id`, `hotel_id`, `room_type_id`, `quantity`, `date`) values (1, 1, 1, -10, '2013-08-13'), (2, 1, 3, -10, '2013-08-25'), (3, 2, 2, 0, '2013-09-26'), (4, 2, 4, 0, '2013-09-27'), (5, 3, 5, 0, '2013-10-28'), (6, 3, 5, 0, '2013-10-09'); create table if not exists `omc_hotel_room_pricelist` ( `id` int(11) not null auto_increment, `hotel_id` int(11) not null, `room_type_id` int(11) not null, `season_id` int(11) not null, `price_normal` decimal(5,2) default null, primary key (`id`) ) engine=innodb default charset=utf8 collate=utf8_swedish_ci; insert `omc_hotel_room_pricelist` (`id`, `hotel_id`, `room_type_id`, `season_id`, `price_normal`) values (1, 1, 1, 1, 50.00), (2, 2, 2, 2, 10.00), (3, 1, 1, 1, 35.00), (4, 1, 1, 2, 70.00), (5, 1, 1, 1, 80.00), (6, 2, 4, 1, 80.00), (7, 4, 7, 2, 80.00), (8, 3, 6, 1, 80.00), (9, 3, 5, 1, 30.00); create table if not exists `omc_hotel_season_period` ( `id` int(11) not null auto_increment, `hotel_id` int(11) not null, `season_id` int(11) not null, `date` date not null, primary key (`id`) ) engine=innodb default charset=utf8 collate=utf8_swedish_ci; insert `omc_hotel_season_period` (`id`, `hotel_id`, `season_id`, `date`) values (1, 1, 1, '2013-08-13'), (2, 1, 1, '2013-08-14'), (3, 1, 2, '2013-08-15'), (4, 2, 1, '2013-08-13'), (5, 2, 1, '2013-08-14'), (6, 2, 2, '2013-08-15'), (7, 3, 2, '2013-08-13'), (8, 3, 2, '2013-08-14'), (9, 3, 1, '2013-08-15'), (10, 1, 1, '2013-08-13'), (11, 1, 1, '2013-08-14'), (12, 1, 1, '2013-08-14'); select candidates.hotel_id, omc_hotel_season_period.season_id, room_1_id, r1_price.price_normal room_1_price, sum(r1_price.price_normal) room_1_total, room_2_id,r2_price.price_normal room_2_price,sum(r2_price.price_normal) room_2_total, omc_hotel_season_period.date (select r1.hotel_id, r1.id room_1_id, r2.id room_2_id omc_hotel_room r1 inner join omc_hotel_room r2 on r1.hotel_id = r2.hotel_id r1.quantity >= 3 , r2.quantity >= 4 , r1.pax_max = 1 , r2.pax_max = 2) candidates left join omc_hotel_season_period on candidates.hotel_id = omc_hotel_season_period.hotel_id , date between '2013-08-13' , '2013-08-15' , omc_hotel_season_period.date <> 0 left join omc_hotel_room_pricelist r1_price on (candidates.hotel_id = r1_price.hotel_id , room_1_id= r1_price.room_type_id) left join omc_hotel_room_pricelist r2_price on (candidates.hotel_id = r2_price.hotel_id , room_1_id = r2_price.room_type_id) r1_price.season_id = omc_hotel_season_period.season_id , r2_price.season_id = omc_hotel_season_period.season_id group candidates.hotel_id, omc_hotel_season_period.date
try , see if u want.
select candidates.hotel_id, omc_hotel_season_period.season_id, room_1_id, r1_price.price_normal room_1_price, sum(r1_price.price_normal) room_1_total, room_2_id,r2_price.price_normal room_2_price,sum(r2_price.price_normal) room_2_total, omc_hotel_season_period.date, (select sum(room1_price.price_normal) omc_hotel_room_pricelist room1_price inner join omc_hotel_room room1 on room1_price.hotel_id = room1.hotel_id inner join omc_hotel_season_period so1 on room1_price.hotel_id = so1.hotel_id , room1_price.season_id = so1.season_id inner join omc_hotel hotel1 on room1_price.hotel_id = hotel1.id so1.date between '2013-08-13' , '2013-08-15' , room1.quantity >= 3 , room1.pax_max = 1 , hotel1.id = candidates.hotel_id group candidates.hotel_id ) room_1_sum (select r1.hotel_id, r1.id room_1_id, r2.id room_2_id omc_hotel_room r1 inner join omc_hotel_room r2 on r1.hotel_id = r2.hotel_id r1.quantity >= 3 , r2.quantity >= 4 , r1.pax_max = 1 , r2.pax_max = 2) candidates left join omc_hotel_season_period on candidates.hotel_id = omc_hotel_season_period.hotel_id , date between '2013-08-13' , '2013-08-15' , omc_hotel_season_period.date <> 0 left join omc_hotel_room_pricelist r1_price on (candidates.hotel_id = r1_price.hotel_id , room_1_id= r1_price.room_type_id) left join omc_hotel_room_pricelist r2_price on (candidates.hotel_id = r2_price.hotel_id , room_1_id = r2_price.room_type_id) r1_price.season_id = omc_hotel_season_period.season_id , r2_price.season_id = omc_hotel_season_period.season_id group candidates.hotel_id 
Comments
Post a Comment