>Hi Naomi. I'm unclear on what "derived tables" are and would appreciate a little clarity on the following code in terms of what it represents and how it works so I can debug it if needed. As always, your help is appreciated:
>
>(1) ChildCount.CountChildren
>(2) count(distinct(ChildrenPK)) as CountChildren from Child group by 1)
>CountChildren on Parent.PK = CountChildren.ParentFK


Hi Abel,

Say, you have two tables Parent and Child and you want to retrieve information Parent - Child information + Count Of Children per parent and Max, Min value of, Say, amount field in Child per Parent.

Say, Parent is Order Header

OrderID OrderDate CustomerID

and Child is Order Details

OrderDetailID OrderID - FK ItemID Qty Price

select Parent.OrderDate, Child. ItemID, Child.Qty, Child.Price, 
OrderSummay.MinAmount, OrderSummary.MaxAmount, OrderSummary.ItemsCount 
from Parent LEFT JOIN Child on Parent.OrderID = Child.OrderID 
LEFT JOIN (OrderID, select Max(qty*Price) as MaxAmount, MIN(qty*Price) as MinAmount, 
Count(*) as ItemsCount from Child group by OrderID) OrderSummary on Parent.OrderID = OrderSummary.OrderID

The query in parenthesis aliased as OrderSummary is called Derived table. This is a technique to add certain information to the parent record and this is what you can use in your case too.

This only works in VFP9.