I have two MySQL tables, with the following structure (I have removed irrelevant columns).

```
mysql> DESCRIBE `edinners_details`;
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| details_id       | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| details_pupil_id | int(11) unsigned | NO   |     | NULL    |                |
| details_cost     | double unsigned  | NO   |     | NULL    |                |
+------------------+------------------+------+-----+---------+----------------+

mysql> DESCRIBE `edinners_payments`;
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| payment_id       | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| payment_pupil_id | int(11) unsigned | NO   |     | NULL    |                |
| payment_amount   | float unsigned   | NO   |     | NULL    |                |
+------------------+------------------+------+-----+---------+----------------+

```

The way the system works is that you order a meal and each meal has a cost, each of these orders are stored in `edinners_details`. An example row would be as follows:

```
mysql> SELECT * FROM `edinners_details` LIMIT 1;
+------------+------------------+--------------+
| details_id | details_pupil_id | details_cost |
+------------+------------------+--------------+
|          1 |            18343 |           25 |
+------------+------------------+--------------+

```

Typically people will pay for these meals in bulk - if they have £40 worth of meals over the course of 20 days, they'll pay that off at the end of the month. Every time they pay, a new row goes into the `edinners_payments` table, an example row for this would be as follows:

```
mysql> SELECT * FROM `edinners_payments` LIMIT 1;
+------------+------------------+----------------+
| payment_id | payment_pupil_id | payment_amount |
+------------+------------------+----------------+
|          1 |            18343 |             20 |
+------------+------------------+----------------+

```

So from these two rows we can see that this person is current £5 in debt - they've had a £25 meal and only paid £20. Over time there will be many rows for each of the users of the system, I can easily work out how much worth of food they've had by doing a simple query such as 

```
SELECT SUM(`details_cost`) AS `meal_total` 
FROM `edinners_details` 
WHERE `details_pupil_id` = '18343';

```

Then to get the amount of money they have paid, I simply do this query:

```
SELECT SUM(`payment_amount`) AS `payment_total` 
FROM `edinners_payments` 
WHERE `payment_pupil_id` = '18343';

```

My ultimate goal is to be able to see who owes the most money, but to loop every user of my `users` table and run these two queries for them, I believe it would be quite slow, so ideally what I would like to do is combine the two above queries into one, and perhaps an additional column which would be (`meal_total` - `payment_total`) which would give me the amount owed. I've tried a few methods to make this work, including joins and sub-queries but they all seem to repeat every relevant line from the `edinners_details` for each of the `edinners_payments` line - so if there were 3 details and 4 payments, you would have 12 rows pulled out which means doing a SUM() on the columns gives me a value which far exceeds what it should be. A good way to demonstrate this would be to run this query:

```
SELECT * FROM (
    SELECT `details_cost` AS `cost` 
    FROM `edinners_details` 
    WHERE `details_pupil_id` = '18343'
    GROUP BY `details_id`
) AS `details`, (
    SELECT `payment_amount` AS `amount` 
    FROM `edinners_payments` 
    WHERE `payment_pupil_id` = '18343'
    GROUP BY `payment_id`
) AS `payment`;

```

which gives me the following result:

```
+------+--------+
| cost | amount |
+------+--------+
|  2.5 |     20 |
|  2.5 |      6 |
|  2.5 |      3 |
|  2.5 |   1200 |
|  2.5 |     20 |
|  2.5 |      6 |
|  2.5 |      3 |
|  2.5 |   1200 |
|  2.5 |     20 |
|  2.5 |      6 |
|  2.5 |      3 |
|  2.5 |   1200 |
|  2.5 |     20 |
|  2.5 |      6 |
|  2.5 |      3 |
|  2.5 |   1200 |
|  2.5 |     20 |
|  2.5 |      6 |
|  2.5 |      3 |
|  2.5 |   1200 |
+------+--------+

```

Adding the SUM into this, as such:

```
SELECT SUM(`details`.`cost`) AS `details_cost`, SUM(`payment`.`amount`) AS `payment_total` FROM (
    SELECT `details_cost` AS `cost` 
    FROM `edinners_details` 
    WHERE `details_pupil_id` = '18343'
    GROUP BY `details_id`
) AS `details`, (
    SELECT `payment_amount` AS `amount` 
    FROM `edinners_payments` 
    WHERE `payment_pupil_id` = '18343'
    GROUP BY `payment_id`
) AS `payment`;

```

Gives me the following result:

```
+--------------+---------------+
| details_cost | payment_total |
+--------------+---------------+
|           50 |          6145 |
+--------------+---------------+

```

If this was working, `details_cost` would be 12.5 and `payment_total` would be 1229, but that is not the case. You can clearly see the repetition in the above result, I apologise that all the costs are 2.5, it makes it a little less obvious, but they are 5 seperate meal orders with 4 payments having been made. Does anyone know how I would go about getting the SUM() of the meal order costs and the SUM() of the payments at the same time?

Thanks