>Hi Naomi. Thanks for the previous info. I'm still a bit confused. I'd appreciate it if you'd explain your derived tables approach in the context of your prior example that used my parent-child-grandchild structure, where you began with "Parent (LibraryICQIntroductions) - PK........". I need to be able to understand the connection(s) between; " (1) ChildCount.CountChildren, (2) Child.* Fields and then (3) the line "count(distinct(ChildrenPK) as CountChildren from Child group by 1). I'm unclear as to what you mean by above no. 2; "Child.* Fields", as well as what you mean by "Child group". Please forgive my ignorance. This is all new to me. Thanks.

Abel,

By Child.Fields I meant you need to specify child table fields here.

One more time - quickly.

Parent table

Customers (CustomerPK Customer Name)

Child Table - Orders

OrderPK CustomerFK OrderDate

Grand Child

OrderDetails

OrderDetailID OrderFK ItemID Qty Price

select Cu.Name, O.OrderDate, OD.*, OC.OrdersCount, ODC.ItemsCount from Customers Cu 
LEFT JOIN Orders O on Cu.CustomerPK = O.CustomerFK 
left join OrderDetails OD on O.OrderPK= OD.OrderFK 
LEFT JOIN (select CustomerFK, count(*) as OrdersCount from Orders 
group by CustomerFK) OC on C.CustomerPk = OC.CustomerFK 
LEFT JOIN (select OrderFK, count(*) as ItemsCount form OrderDetails group by OrderFK) ODC on O.OrderPk = ODC.OrderFK

Note the derived tables here to get counts of Orders per customer and count of Items per order in the same select.