Walter,
As I said in earlier post this construction is illegal. Thanks you were the only one to reply my question.
Since there are no other replies and I couldn't find an answer I'll accept it as "no solution" and won't bend it more. Current workaround while not one SQL, is fast and adequate for me. Thanks.
Cetin
>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
