Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Aggregate from multipl tables
Message
From
16/01/2000 07:25:02
Walter Meester
HoogkarspelNetherlands
 
 
To
15/01/2000 11:40:07
Cetin Basoz
Engineerica Inc.
Izmir, Turkey
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Miscellaneous
Thread ID:
00318566
Message ID:
00318711
Views:
26
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
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform