>I'm getting "GROUP BY clause is missing or invalid.". That's what I was getting
>before. I don't see what's invalid about it. ??
>

I think I was too quick and wrong. Something like

select mh.*, nvl(mi.Total,0) as Total from ;
mh left join (select MenuItems.ID, ;
count(*) as Total from MenuItems group by 1) mi on mh.ID = mi.ID

would work.


>
>>>I have a header table and a child table.
>>>
>>>Not all header records have a child record.
>>>
>>>I want to pull all headers and also a column with the count of child records.
>>>
>>>What I have isn't working. Using VFP 9:
>>>
>>>

>>>SELECT mh.Caption, mh.Icon, COUNT(mi.*) AS Total;
>>>	FROM MenuHead MH;
>>>	JOIN MenuItems mi on mi.Parent = mh.id;
>>>	GROUP BY mh.Caption, mh.Icon;
>>>	INTO CURSOR Temp1
>>>

>>>
>>>Anyone?
>>>
>>>Thanks
>>

>>SELECT mh.Caption, mh.Icon, sum(iif(IsNull(mi.ID),0,1)) AS Total ;
>>>	FROM MenuHead MH;
>>>	LEFT JOIN MenuItems mi on mi.Parent = mh.id;
>>>	GROUP BY mh.Caption, mh.Icon;
>>>	INTO CURSOR Temp1
>>>