Is there a way to optimize this mysql query...?
- by SpikETidE
Hi Everyone...
Say, I got these two tables....
Table 1 : Hotels
hotel_id hotel_name
1 abc
2 xyz
3 efg
Table 2 : Payments
payment_id payment_date hotel_id total_amt comission
p1 23-03-2010 1 100 10
p2 23-03-2010 2 50 5
p3 23-03-2010 2 200 25
p4 23-03-2010 1 40 2
Now, I need to get the following details from the two tables
Given a particular date (say, 23-03-2010), the sum of the total_amt for each of the hotel for which a payment has been made on that particular date.
All the rows that has the date 23-03-2010 ordered according to the hotel name
A sample output is as follows...
+------------+------------+------------+---------------+
| hotel_name | date | total_amt | commission |
+------------+------------+------------+---------------+
| * abc | 23-03-2010 | 140 | 12 |
+------------+------------+------------+---------------+
|+-----------+------------+------------+--------------+|
|| paymt_id | date | total_amt | commission ||
|+-----------+------------+------------+--------------+|
|| p1 | 23-03-2010 | 100 | 10 ||
|+-----------+------------+------------+--------------+|
|| p4 | 23-03-2010 | 40 | 2 ||
|+-----------+------------+------------+--------------+|
+------------+------------+------------+---------------+
| * xyz | 23-03-2010 | 250 | 30 |
+------------+------------+------------+---------------+
|+-----------+------------+------------+--------------+|
|| paymt_id | date | total_amt | commission ||
|+-----------+------------+------------+--------------+|
|| p2 | 23-03-2010 | 50 | 5 ||
|+-----------+------------+------------+--------------+|
|| p3 | 23-03-2010 | 200 | 25 ||
|+-----------+------------+------------+--------------+|
+------------------------------------------------------+
Above the sample of the table that has to be printed...
The idea is first to show the consolidated detail of each hotel, and when the '*' next to the hotel name is clicked the breakdown of the payment details will become visible... But that can be done by some jquery..!!! The table itself can be generated with php...
Right now i am using two separate queries : One to get the sum of the amount and commission grouped by the hotel name. The next is to get the individual row for each entry having that date in the table. This is, of course, because grouping the records for calculating sum() returns only one row for each of the hotel with the sum of the amounts...
Is there a way to combine these two queries into a single one and do the operation in a more optimized way...??
Hope i am being clear.. Thanks for your time and replies...
