Select rows in left join which depend on sum of a field in the other table?

Question

Im trying to write a SQL left outer join query where the left rows are selected based on the sum of a field in rows in the other (right) table. The other table has an id field that links back to the left table and there is a one-to-many relationship between left and right tables. The tables (simplified to relevant fields only) look like this:

left_table:

+--------+
| id     |
| amount |
+--------+

right_table:

+-------------------+
| id                |
| amount            |
| left_table_row_id |
+-------------------+

Basically the right table's rows' amount fields have fractions of the amounts in the left table and are associated back to the left_table, so several right_table rows might be linked to a single left_table row.

Im trying to select only left_table rows where left_table.id=right_table_id and where the sum of the amounts in the right_table's rows with linked id are equal to left_table.amount. We can't use aggregate in a WHERE clause and I had no luck with using HAVING. I hope that makes sense.

Answer 1

You can filter with a correlated subquery:

select l.*
from left_table l
where l.amount = (select sum(r.amount) from right_table r where r.id = l.id)