Valentina 2 beta

[Ruslan Zasukhin]

On 3/3/05 4:02 PM, "Ian Händschke" <ian at milkymedia.com> wrote:

> Sure.
> 
> Okay. I¹m trying to explain it as good as possible and my english is
> not the best ­ so please have patience with me! I¹m working on a Music
> CD-Database on CD-ROM. I got about 50.000 (music-) titles, which are
> linked with an n:m relation to about 1.500 CDs
> 
> table cds ( cd_id, cd_ordernum, cd_title )
> table titles ( title_id, title_name, lenght )
> table cds_titles ( cd_id, title_id, track )
> 
> Now there is a base of search-keywords, e. g. "evening mood" oder
> "piano" or "easy listening". Because some of the keywords are about the
> same (e. g. "aerobics" and "gymnastic") I¹m using a 1:n-relation to
> group them and a n:m-relation to asign them to titles. The keywords are
> also divided into categories like "instruments", "composer" or "mood".

Not sure, but it seems here you can use feature of Valentina
    index by words.

Valentina self will build index with words, so this will be not your
headache. And no need special program this.
 
Although it seems you have strict set of key words and their groups...

> table keyword_categories ( categorie_id, categorie )
> table keywords ( keyword_id, group_id, categorie_id, keyword )
> table keyword_groups ( group_id )
> table title_keywords ( title_id, group_id )
> 
> 
> Now I want to display the content of a CD like this:
> cds_titles.track, titles.title_name, titles.lenght, keywords.count(),
> keywords.keyword
> 
> "keywords.count" shall be the following:
> If there are keywords assigned that are categorized "composer", I want
> the count of keywords
> 
> "keywords.keyword" shall be the following:
> If there are keywords assigned that are categorized "composer", I want
> the first keyword (which is the name of the composer)

What is composer?

> Please show me how you solve this using an outer left join only.
> Thanks for your help and patience ­

Why you think that OUTER JOIN is a solution ?
You want quite complex query.

In SQL92 it may require sub-selects as I see. Because you want calculate
COUNT for table keywords (right?).

YOU WANT express this as single Query ?
You have this query for other DBMS ?
Or we need invent it ?
Just it seems query will not be simple.

PROBLEMS which I see:
    * you want normal fields
            cds_titles.track, titles.title_name, titles.lenght

    * but you also want aggregate column  COUNT

    * and you want ONE OF key words...

I afraid to get result you need do several queries,
May be use separate tmp table to accumulate result

I think we can discuss your task on Valentina beta list more detailed.
Really 2.0 may be better choice for such task...


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