Daniel Samuels - 7 months ago 12

SQL Question

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`

`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`

`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`

`meal_total`

`payment_total`

`edinners_details`

`edinners_payments`

`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`

`payment_total`

Thanks

Answer

The following works for me, although it looks ugly. In a MySQL DB:

```
SELECT
t1.p_id, t1.cost, t2.amount
FROM
(SELECT
details_pupil_id AS p_id, SUM(details_cost) AS cost
FROM
edinners_details
GROUP BY
details_pupil_id) t1,
(SELECT
payment_pupil_id AS p_id, SUM(payment_amount) AS amount
FROM
edinners_payments
GROUP BY
payments_pupil_id) t2
WHERE
t1.p_id = t2.p_id
/* Getting pupils with dinners but no payment */
UNION
SELECT
details_pupil_id, SUM(details_cost) cost, 0
FROM
edinners_details
WHERE
details_pupil_id NOT IN (SELECT DISTINCT payment_pupil_id FROM edinners_payments)
GROUP BY
details_pupil_id
/* Getting pupils with payment but no dinners */
UNION
SELECT
payment_pupil_id, 0, SUM(payment_amount)
FROM
edinners_payments
WHERE
payment_pupil_id NOT IN (SELECT DISTINCT details_pupil_id FROM edinners_details)
GROUP BY
payment_pupil_id
```