General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
>
>
>select master.*, cnt(detail.field) as cnt;
> from master;
> join detail on master.key=detail.key;
> group by master.key
>
>
>
>Old Visual FoxPro can do this, but it is out of all SQL standards.
Opinions differ.
this is not a opinion, it is a fact.
You can say that when group by have a fields list it's a Primary key
the ambiguity is resolved,
but i do not known a database package that use this set property directly;
then the only way is to grouping memo or aggregate memo,
- grouping ( that is sorting ) is hard because the memo do not have a short limit,
- aggregate can to be MAX(),MIN(),FIRST(),LAST(),
for MAX() and MIN() is need sorting, then the way is cut,
only FIRST and LAST are possible.
Before VFP8, VFP implement LAST() implicitly ,
but AFAIK only MS Access implement FIRST() explicitly.
In fact the FIRST() and LAST() is very usefull on general case,
but some mysterious reason they are not implemented.
MSSQL2000 implement FIRST() with primary key group like a optimization, but you must write a correct autosubquery for get this!
Perhaps yukon do new somethings.
>- try a join of the parent table with an extra view which would keep just the document key and the cnt(*) from the child table
>
>
>Yes, it is the correct choice.
>It is efficient and the extra view is fast and usable for other task.
That's what I decided to do, thanks for chiming in. Grazie, amico.
E' un piacere.
Fabio
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only