Page 2 of 2
problem is solved
Posted: Sat Jan 21, 2006 4:06 am
by namitjung
thnak you all for your response. i am using raghavan20 idea.Thank you very much
Posted: Sat Jan 21, 2006 4:50 pm
by duk
try to relaxe, get a pen and a paper, see the raghavan20 idea design it in the paper... and try to improve it...
its good to first design what you want and what you will do, in a paper...
good luck!!!
Got problem again
Posted: Wed Feb 15, 2006 2:07 am
by namitjung
Hi all,
I took raghav idea and now i got a liitle bit problem
I got problem to display the records in the browser.My table design is as follows:
table Name:
hotel
hotelid hotelname
1 A
2 B
3 C
4 D
Roomtype
roomtypeid hotelid rommtypetitle normalrate
1 1 Room A 40
2 1 Room B 30
3 1 Room c 20
4 1 Room D 25
5 2 Room E 50
6 2 Room F 55
Discountrates
rateid roomtypeid HotleId startdate enddate discountrate
1 1 1 2006/02/03 2006/02/06 38
2 1 1 2006/02/08 2006/07/09 39
3 1 1 2006/02/15 2006/07/10 40
I Am creating hotel site.All hotel details are stored in hotel table and Roomtypes and it's normal rate of each hotel is stored in roomtype table.Since some hotel may decrease their rates in weekends or in some special occasions those rates (If any) are stored in the discountrates table.Now i have problem for displaying the discounted rates.
In home page there will be a search in which checkin date and checkout date would be selected.After that by using checkedin date and checkedout date it should show the records in this format:
let's suppose check in date is 2006/02/05 and check out date is 2006/02/10
Roomtypetitle 2006/02/05 2006/02/06 2006/02/07 2006/02/08 2006/02/09 2006/02/10
Room A 38 38 40 39 39 40
Room B 30 30 30 30 30 30
Since rate for the 2006/02/05 and 2006/02/06 is exists in discount rates table it would show discountrates.There is no rate for 2006/02/07,so it will show the normal rate stored in roomtype table.Same thing will happen for the rest of the dates.If there is no rates
in discountrate table normal rate would be use.
What kind of query i should use?
Please help me out
Thank You
My Try
Posted: Wed Feb 15, 2006 6:33 am
by namitjung
I tried to use inner join It works only when there is record exists for that roomtype in roomrate table.Then i tried left join and it is returning duplicate records.I don't know what to do?
Thank You
Posted: Wed Feb 15, 2006 9:34 am
by feyd
post the SQL you've tried and your actual (SQL creation format) table structures please.
Got the solution
Posted: Thu Feb 16, 2006 7:04 am
by namitjung
Hello all,
Finally, I got the solution for this.My soultion is based on procedure and multiple recordsets..