Automatically generate rows

[jackdoe77]

Hi,
Could someone assist me how to automatically generate rows.
I have 2 tables :
Table1 : id, cust_name, period, date_due, amount
Table2 : id, subid, seq, date_due, date_pay, amount_paid

Table1 record : 1, jack, 12, 15/05/2010, 500
Table2 record:
1, 1, 1, 15/05/2010, 27/05/2010, 500
1, 2, 2, 15/06/2010, 30/06/2010, 300
1, 3, 2, 15/06/2010, 05/07/2010, 200
1, 4, 3, 15/07/2010, 01/08/2010, 300

I need to retrieve records that payment is due per today.
jack, 15/05/2010, 0
jack, 15/06/2010, 0
jack, 15/07/2010, 200 (500 - 300)
jack, 15/08/2010, 500 -> due payment (no record on Table2)

My SQL would be like this :
SELECT a.cust_name, b.date_due, due = a.amount - sum(amount_paid)
FROM table1 a
LEFT JOIN table2 b ON b.id = a.id
GROUP BY a.cust_name, b.date_due, a.amount

My problem is, I can't generate the last row since there is no payment in Table2 for 15/08/2010 due date. How can I generate this record on-the-fly (without creating the record in the table). It's only for reporting purpose.

Thanks in advance.

 

[simian336]

You could create a 3rd table with all the expected payments then UNION it to the old query...


SELECT a.cust_name, b.date_due, due = a.amount - sum(amount_paid)
FROM table1 a
LEFT JOIN table2 b
ON b.id = a.id
GROUP BY a.cust_name, b.date_due, a.amount

union

SELECT a.cust_name, b.date_due, due = a.amount - sum(amount_paid)
FROM table1 a
LEFT JOIN table3 b
ON b.id = a.id
GROUP BY a.cust_name, b.date_due, a.amount

That should work..

Simi