You need to use a LEFT OUTER JOIN which lists all the entries in the
InvTot table whether or not there are entries in the payments table. It
will fill the payment side with nulls
CREATE VIEW `VWA9iInvHist` (InvNum, InvDate, Sale, TransDate, Payment) AS +
SELECT t1.InvNum, t1.InvDate, t1.Sale, t2.TransDate,
(IFNULL(t2.Payment,0.00)) +
FROM vwInvTot +
LEFT OUTER JOIN VWA9InvPmt t2 ON t1.InvNum = t2.InvNum
Albert
On 08/04/2011 11:11 AM, J BLAUSTEIN wrote:
I have removed the extra ")" - I'm trying to combine both table with
all the information - but I need to allow for when no payments are made
J**
