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.
Advertisement
Answer
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)