On Wednesday 15 October 2003 02:01, Muhyiddin A.M Hayat wrote:
>
> Table Billing:
>
> id trx_date trx_time depart payment_method
> billing_amount amount_paid balance creator 1 10/09/2003
> 21:55:02 Resto Visa 13.800,00
> 10.000,00 3.800,00 middink
>
> Table Payment
>
> id r trx_date trx_time descriptions
> payment_method amount creator 1 10/08/2003 18:17:40
> Payment Cash 2.000,00 middink
>
>
> I would like to create "View " from above table with result look like:
>
>
> trx_date trx_time descriptions
> payment_method debet
> credit balance creator 10/09/2003 21:55:02 Resto
> Billing 13.800,00 Paid: 10.000,00 Visa
> 3.800,00 3.800,00 middink 10/08/2003
> 18:17:40 Payment
> Cash 2.000,00
> 1.800,00 middink
I'm not sure the above makes sense. I don't see any way to connect that
particular payment to that particular bill. You also seem to have two
different types of information in the billing table (billing *and* payment).
Also, the payment seems to be occurring before the bill - which can happen,
but there doesn't seem to be any account it is paid into.
Generally, there are two ways to do this:
1. Set-returning functions (see the manuals and
http://techdocs.postgresql.org)
2. A transaction/account page that you keep up to date in exactly the sort of
format you want in your view. Triggers can make sure this happens
automatically.
Unfortunately, if you're stuck with the tables you've shown us then I don't
think you can solve your problem.
-- Richard Huxton Archonet Ltd
