>Hi All,
>
>I have a parent table and several child tables. All child tables can have mutiple records per parent record and some parents will not have a record in all child tables. Can I create a view that has some basic fields of the parent table and for each parent have the number of records in each of the child tables.
>
>Thanks in advance
select parent.keyid, parent.field1, ..., parent.fieldN, count(child1) Kount1, count(child2) Kount2 from parent left join child1 on parent.keyid = child1.parent_id left join child2 on parent.keyid = child2.parent_id where somefilterhere group by parent.keyid
You will get NULL values for Kounts where parent record has no child records.
Mark McCasland
Midlothian, TX USA