Cetin,
What do you mean by illegal ? As far as I can see this should work. Where does this go wrong ? The distinct statement within the count should handle the recurance of the same row. Therefore the count statments counts the different PK in the child table.
I'm glad you've found another solution, but i'm curious why this doesn't work for you.
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
