Cetin,

You may look into the followiung direction. I don't have any tables a hand right now to test this but I think the following approach might work.

SELECT myparent.iid, sum(IIF(ISNULL(T1.pid),0,1) as t1.pid,;
   sum(IIF(ISNULL(T2.pid),0,1) as t2.pid,;
   sum(IIF(ISNULL(T3.pid),0,1) as t3.pid,;
   sum(IIF(ISNULL(T4.pid),0,1) as t4.pid ;
   FROM myparent LEFT JOIN T1 ON myparent.iid = t1.pid ;
                 LEFT JOIN T2 ON myparent.iid = t2.pid ;
                 LEFT JOIN T3 ON myparent.iid = t3.pid ;
                 LEFT JOIN T4 ON myparent.iid = t4.pid ;
   GROUP BY Myparent.iid 

maybe the sum() construct could be replace by count (distinct Tx.Pk), but i haven't tested this.

This SQL statement might be pretty slow, an xBase approach might be better.

Walter,



>Hi folks,
>I have brain dead by now :)
>I have a parent table say myParent with PK "iid". There are few child tables (not really childs but have "pid" as FK referencing "iid"), say T1, T2, T3, T4.
>I need to collect rowcounts in each table per "iid" (CrossTab).
>

>myParent.iid   t1.pid   t2.pid    t3.pid   t4.pid
>------------   ------   ------    ------   ------
>1                 1        2         1        1
>2                 1        3         5        2
>3                 1        3         5        3
>4                 2        4         7        4
>5                 4                  7        6
>6                 4                           6
>7
>8
>9
>
>* Desired output
>
>pid   Cnt1  Cnt2  Cnt3  Cnt4
>---   ----  ----  ----  ----
> 1      3          1     1
> 2      1     1          1
> 3            2          1
> 4      2     1          1
> 5                 2
> 6                       2
> 7                 2
> 8
> 9

Any idea how I could do this just with one SQL ? TIA
>Cetin