SQL join with related counts

[Ruslan Zasukhin]

on 8/20/03 11:39 PM, Tim Davis at timdavis at amug.org wrote:

>>>>> I'm trying to list all rows of a table, with the quantities of its
>>>>> child table records. Here's an example of what I've tried, which
>>>>> crashes the server but not the app. (I'm using VServer and V4RB.)
>>>>> 
>>>>> Select Parent.Field1, Parent.Field2, Count(Child1.ID),
>>>>> Count(Child2.ID), Count(Child3.ID)
>>>>> From Parent, Child1, Child2, Child3
>>>>> Order By Parent.Field1
>>>> 
>>>> You need to use GROUP BY Parent.Field1
>>> 
>>> The following changes produce the same results:
>>>   adding Group By Parent.Field1
>>>   same as above, without Order By Parent.Field1
>>> VServer crashes each time.
>> 
>> Wait!!! You do not correct query!!!
>> 
>> IF you group by Parent.Field1, you can SELECT only Parent.Field1 self
>> And aggregate function on it.
>> 
>> Select Parent.Field1, Count(Parent.Field1),
>> From Parent
>> GROUP By Parent.Field1
> 
> Hi Ruslan,
> 
> That would not obtain all the information I need. Is there another way
> to achieve these values other than group? (I had been doing 4 different
> queries, 1 for each table, but I wanted to simplify and speed up
> performance.)

Hi Tim,

As far as I know, any DBMS cannot do this for several tables in the same
time. Because you want COUNT different groups in fact.

In 2.x I hope we get in Valentina feature that will allow do this.
This must be some kind of BaseObject Method   GetLickedCount()
And it will be use LINK { ObjectPtr, or RDB, or others } to get this info,
May be even from indexes. Although this still will be not super fast..on the
other hand should be faster of GROUP BY.

-- 
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
-------------------------------------------------------------