Cetin,

I think the following should be right.

SELECT myparent.iid, count(distinct T1.Pk) as t1.pid,;
   count(distinct T2.Pk) as t2.pid,;
   count(distinct T3.Pk) as t3.pid,;
   count(distinct T4.Pk) 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 

NOTE in the first line the SELECT statement counts the different Primarykey values of the related tables to avoid multiple counts of the same records, which is caused by the 4 left joins.

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