SQL join with related counts

[Ruslan Zasukhin]

on 8/22/03 1:34 AM, Tim Davis at timdavis at amug.org wrote:

>> So you can do COUNT for 1 table,
>> And ask me if there is way for 4 tables in single query?
>> 
>> I do NOT see such way.
> 
> Hi Ruslan,
> How about this, three related record counts from one parent record?
> 
> Example:
> Select Count(Child1.ID), Count(Child2.ID), Count(Child3.ID)
> From Parent, Child1, Child2, Child3
> Where Parent.Field1='T1111'
> 
> Would this work? .. or do I have the syntax wrong. I tried it in viSQL
> and its crashes.

It is wrong, because you can use COUNT() only on field that is in GROUP BY.
You even do not have GROUP BY here.

> It seems to make logical sense to me if one can Join values of multiple
> tables, one can also retrieve multiple child Counts and other
> functions. If you can code something like this into Valentina it would
> be very cool!

Yes, I know.
I see this as:

1) you define in the Table "T"  4 BaseObject methods:
            ChildCount2 = "ChildCount(T2)"
            ChildCount3 = "ChildCount(T3)"
            ChildCount4 = "ChildCount(T4)"
            ChildCount5 = "ChildCount(T5)"

    ------------------------------------------
    SELECT ParentID,    COUNT( ChildCount2 ),
                        COUNT( ChildCount3 ),
                        COUNT( ChildCount4 ),
                        COUNT( ChildCount5 )
    FROM T
    GROUP BY ParentID
    ------------------------------------------


**********************
For now I think you need do

1) build 4 TMP tables. Counting only one table.
You get 4 Tables with 2 fields.
Each table has ParentID field, so you can JOIN them all.
May be you will need FULL OUTER Join.

Maybe simple build complete table manually.
This will be easier to do if have 4 tables sorted by ParentID

-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------