Hi,
the idea is to make a payment against an invoice and not a item.
A invoice will have many items
A invoice will have many payments
The payment will be made against an invoice.
So for example the display I WANT is as below in date order and mix records:
Invoice number: 0001 // this comes from tbl_invoice_number
Date,Item, unit price, debit, credit, total
19/03/2006,basingstoke 1, 220.00, 220.00,0.00, 220.00 // this comes from tbl_invoice_item
19/03/2006,payment,0.00,0.00,10.00,210.00 // this comes from tbl_invoice_payment
20/03/2006,basingstoke 2, 220.00, 220.00,0.00, 430.00 // this comes from tbl_invoice_item
21/03/2006,payment,0.00,0.00,30.00,400.00 // this comes from tbl_invoice_payment
the tables "tbl_invoice_item" and "tbl_invoice_payment" both have a date_in field which is the date, the record was inserted. I want to display the list in date order as above.
Thats the whole reason for writing this query, otherwise I could have output the tbl_invoice_item first and tbl_invoice_payment afterwards and the display would then be like:
THIS IS WHAT I DON'T WANT:
Invoice number: 0001 // this comes from tbl_invoice_number
Date,Item, unit price, debit, credit, total
19/03/2006,basingstoke 1, 220.00, 220.00,0.00, 220.00 // this comes from tbl_invoice_item
20/03/2006,basingstoke 2, 220.00, 220.00,0.00, 440.00 // this comes from tbl_invoice_item
19/03/2006,payment,0.00,0.00,10.00,430.00 // this comes from tbl_invoice_payment
21/03/2006,payment,0.00,0.00,30.00,400.00 // this comes from tbl_invoice_payment