mysql filter problem

mysql filter problem is project number 281394 posted at GetAFreelancer.com. Click here to post your own project.

Status:	Closed (Selected Service Provider)
Selected Providers:	selected service provider srahmant
Budget:	$30-250
Created:	07/01/2008 at 6:54 EDT
Bidding Ends:	07/05/2008 at 6:54 EDT
Project Creator:	wipeskim   Buyer Rating: (2 reviews)
Description:	Hi , I need some help filtering results on mysql I have a table with : Hotel { Name of the hotel } start_date { Date When season starts } end_date { Date When season ends } room { Room that the hotel has } Price { Price of the room for the hotel } contract_id { Contract reference number for hotel } Rate_base { Prices per Person = PP / Prices for Unit = PU } The values that i output from the table is as follows or please check attach mysql table. Room for 2 People Hotel: Bayside | ROOM: Studio | PRICE: 20 | RATE BASE: PU Hotel: Bayside | ROOM: One bedroom apartment | PRICE: 40 | RATE BASE: PU Hotel: Sheraton algarve | ROOM: Twin | PRICE: 15 | RATE BASE: PP Hotel: Sheraton algarve | ROOM: Suite | PRICE: 25 | RATE BASE: PP Hotel: Savoy Hotel | ROOM: Twin Room | PRICE: 9 | RATE BASE: PP Hotel: Savoy Hotel | ROOM: Junior Suite | PRICE: 16 | RATE BASE: PP It should have a filter to identify when rate base is PP it multiplies by the number of people wich in this case is 2 so the price is {price} * 2 when rate_base is PP when rate_base = PU there’s no need to multiplie by number of pax; can anyone sort this out? The correct output results ordered by price for each unit shoud be the ones mentioned below, please results should output what units are the cheapest and not wich record is cheaper. I will give example how it’s done when rate_base is = PU and PP Room for 2 People Hotel: Savoy Hotel | ROOM: Twin Room | PRICE: 9 | RATE BASE: PP | ex: 9*2 = 18 Hotel: Savoy Hotel | ROOM: Junior Suite | PRICE: 16 | RATE BASE: PP ex: 16* 2 = 32 Hotel: Bayside | ROOM: Studio | PRICE: 20 | RATE BASE: PU ex: 20 Hotel: Bayside | ROOM: One bedroom apartment | PRICE: 40 | RATE BASE: PU Ex: 40 Hotel: Sheraton algarve | ROOM: Twin | PRICE: 15 | RATE BASE: PP Ex: 15*2 = 30 Hotel: Sheraton algarve | ROOM: Suite | PRICE: 25 | RATE BASE: PP Ex: 25*2 = 50 Additional files submitted: zrates.sql
Job Type:	Data Processing PHP
Database:	MySQL
Operating system:	MS Windows
Bid count:	19
Average bid:	$ 51