Valentina 2 beta

[Ruslan Zasukhin]

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

>> Why you think that OUTER JOIN is a solution ?
>> You want quite complex query.
>  
> With Valentina this is the best I get to work:
> (let¹s have the categorie "composer" the categorie_id=6 and the cd_id=87)
> 
> SELECT  
> cds_title.track, title.title_name, keywords. keyword
> FROM cds_ title, title, title_keywords, keywords
> WHERE  title.title_id=cds_title.title_id
> AND title.title_id*= title_keywords.title_id
> AND title_keywords.group_id*=keywords.group_id
> AND cds_titles.cd_id=87
> AND (keywords.categorie_id=6)
> ORDER BY cds_title.track
> 
> An interisting fact is, that valentina returns min. 1 row for each record with
> NO left joined keyword and 2 if there is only keyword assigned...:
> 
> -- "--------------------------------"
> -- "Found: 103" 
> -- "1    Country Affairs        "
> -- "2    Walkin'        "
> -- "3    Dusty Road        "
> -- "4    Texas Trail        "
> -- "5    Cross Country        "
> -- "6    Oak Ridge Sunset        "
> -- "7    Saturday Night Saloon        "
> -- "8    Fast Food        "
> -- "9    Going West        "
> -- "10    Classical Frontier        "
> -- "11    Truck Tramper Blues        "
> -- "12    Sweet Rodeo Queen        "
> -- "13    Rockin'        "
> -- "14    Hot Texas Chili        "
> -- "15    Coming Home To The Ranch        "
> -- "16    Kentucky Flair        "
> -- "17    Lucky Trucky        "
> -- "18    Wonderful Hawaiian Isle    Clift, Sten    "
> -- "18    Wonderful Hawaiian Isle        "
> -- "19    Tahiti Flower    Clift, Sten    "
>  
> If Valentina had a GROUP-function (like MySQL or SQLite) I could modify the
> Query like this and get nearly what I wanted... so in fact my problem is not
> JOINING but GROUPING:

Valentina 1.x do have GROUP BY.

I wonder if you have read our docs or at least
    http://paradigmasoft.com/kernel.html
 
> SELECT  
> cds_title.track, title.title_name, keywords.keyword
> FROM cds_ title, title, title_keywords, keywords
> WHERE  title.title_id=cds_title.title_id
> AND title.title_id*= title_keywords.title_id
> AND title_keywords.group_id*=keywords.group_id
> AND cds_titles.cd_id=87
> AND (keywords.categorie_id=6)
> GROUP BY cds_title.track
> ORDER BY cds_title.track
> 
> Again interisting: Valentina ignors the "group" but gives no error.

Valentina 1.x can be quite on errors.

Ian, Valentina 1.x, and Valentina 2.0 (even more strictly)
works by SQL92 standard.

Standard say, that you CANNOT mix in the SELECT of GROUP BY query
Normal columns that are not listed in GROUP BT and agg funcs

    SELECT f1, f2, count( f2)
    FROM T
    GROUP BY f2

This is MISTAKE, because f1 cannot be here. Only f2 and COUNT(f2) can be in
the SELECT list.


>> In SQL92 it may require sub-selects as I see. Because you want calculate
>> COUNT for table keywords (right?).
>  
> No. I mean "yes" ­ I want a keyword count. But e.g. with MySQL it would not
> take multiple selects:
> (The tablenames are different ­ please don¹t mind! begriff=keyword,
> gruppe=group) 
> 
> SELECT cdt.track, t.titel_de, sum(b.begriff_id/b.begriff_id), b.begriff
> FROM cds_titel cdt
> LEFT JOIN titel t ON t.titel_id=cdt.titel_id
> LEFT JOIN titel_begriffe tb ON tb.titel_id=cdt.titel_id
> LEFT JOIN begriffe b ON b.begriff_gruppen_id=tb.begriff_gruppen_id AND
> b.begriff_kategorie_id=6
> WHERE cdt.cd_id=87
> GROUP BY cdt.track
> ORDER BY cdt.track;

Hmm, I remember that mySQL do not follow to that rule of SQL92.

> That¹s it. Maybe not elegant but it works. (btw: the tableshortcuts are great
> stuff ­ will valentina 2 have that, too?)

We have them in 1.x also.

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

Well, Ivan, I am sure we will find working query for you in 2.0.

For now you need first of all, install it correctly and be able run
examples.

P.S. I am not sure, it seems beta list is down. I will contact ISP

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