>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.Parent),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
>