>That is what I have been trying but I keep getting weird results.
>Firstly I am not familiar with the COUNT(child1.*) part. Do you mean the asterisk literally or a field name. I get a syntax error with that.
>It seems that this solution works as long as a child table does not contain more than 1 record per parent. Once a child table has more than 1 record per parent many unexpected results occur.
I just tried this on 3 large tables and found it is faster to break it up into multiple SELECTS:
select parent.keyid, parent.field1, ..., parent.fieldN, count(child1.somefield) Kount1 from parent left join child1 on parent.keyid = child1.parent_id where somefilterhere group by parent.keyid into cursor crsTemp1
select parent.keyid, parent.field1, ..., parent.fieldN, count(child2.somefield) Kount2 from parent left join child2 on parent.keyid = child2.parent_id where somefilterhere group by parent.keyid into cursor crsTemp2
select crsTemp1.*, crsTemp2.* from crsTemp1 join crsTemp2 on crsTemp1.KeyID = crsTemp2.KeyID
Mark McCasland
Midlothian, TX USA