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

1. 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.
2. 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...