>SELECT Customer.CustomerID, Customer.CustName, Orders.OrderID, ; > Orders.OrderDate, Orders.Destination ; > FROM Customer ; > INNER JOIN orders ON Orders.CustomerID = Customer.CustomerID ; > AND OrderDate = ( SELECT MAX(OrderDate) FROM Orders Recent WHERE Recent.CustomerID = Orders.CustomerID)This gives me a different error - "SQL: Subquery nesting is too deep." Error 1842. Is this something that was improved with VFP 9?
>SELECT Customer.CustomerID, Customer.CustName, Orders.OrderID, ; > Orders.OrderDate, Orders.Destination ; > FROM Customer ; > INNER JOIN orders ON Orders.CustomerID = Customer.CustomerID ; > WHERE OrderDate = ( SELECT MAX(OrderDate) FROM Orders Recent WHERE Recent.CustomerID = Orders.CustomerID) >UNION >SELECT Customer.CustomerID, Customer.CustName, Orders.OrderID, ; > Orders.OrderDate, Orders.Destination ; > FROM Customer ; > LEFT JOIN orders ON Orders.CustomerID = Customer.CustomerID where orders.OrderID IS NULLThis works, but will be annoying to maintain - I may have to go back to the three step process to keep my sanity.