Dennis,

Daniel's advice to use a LEFT JOIN is exactly right, and to go a little further with the DELETED() issue, VFP SQL opens tables again in different work areas under different names so you can't really specify or tell which table will be evaluated by the DELETED() function, giving unpredicatble results. Same advice goes for RECNO().


>SELECT Invoices.invoiceid, Invoices.ctype, Invoices.i_TranID, ;
> Invoices.s_date, Invoices.e_date,;
> Customers.Addressid AS cid, Products.prod01 AS cProd1, ;
> Products.prod02 AS cProd2, Products.prod03 AS cProd3, ;
> Address.Address1 AS Address1, Address.Address2 AS Address2;
>FROM rm!invoices;
>INNER JOIN rm!invdetail on Invoices.invoiceid = Invdetail.invoiceid;
>INNER JOIN rm!customers on Invoices.customerid = Customers.customerid;
>INNER JOIN rm!Address on Address.AddressID = Customers.AddressID;
>INNER JOIN rm!products on Products.productid = Invoices.productid;
>WHERE Invoices.ctype = 'INVOICE' AND Invoices.posted <> 'Y' AND NOT DELETED();
>GROUP BY Invoices.InvoiceID ;
>ORDER BY Invoices.s_date ;
>INTO CURSOR Temp1
>
>*** The problem I have with this is:
>if there is a record in the Invoices table that qualifies for the "WHERE" but there is no record in the Invdetail table this code does not give me the record in the Invoices table.
>How can I rewrite this to ignore the fact that there is no record in the Invdetail table?
>
>Thanks Dennis