I have go DB structure like here: db-fiddle

I'm looking for some solution that in View for charge_line_tab table will add information if charge line is paid or not, based on previous records in payment_tab.

Not sure but what I think what should be done is calculate sum of previous charge_lines in previous charge_tab and if sum is smaller then sum of total lines in payment_tab for specific user that payment_line should be marked as paid.

Not sure if this is possible at all but it is possible to add also information which payment from payment_tab paid specific charge_line?

What I have in my mind the corner case is that one line was partially covered by one payment so for some payment_line there should be returned to payments.

I think for that solution subselect isn't good idea ;). Maybe windows function but I'm not sure.

Please advice.

EDIT: Example result is view on charge_line_tab which could look like:

Exptected result on charge_line_tab would look like:
charge_line_id, charge_id, description, amount, paid (0 - not paid, 1 - paid, 2 - partially paid), payments_id (this is array of ids of payment_tab that conver that payment, that should be an array because in some cases two or more paymentns can cover one line)
  • Could you add the expected result? – McNets Jan 4 at 10:55
  • @McNets edited, take a look again, thanks – Robert Jan 4 at 12:04

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.