Re: [libreoffice-users] Re: base - join same table multiple times

On 01/23/2012 02:31 PM, Werner F. Bruhin wrote:
> Hi Dan and Calvin,
>
> First of all thanks for the quick response.
>
> Based on what suggested I came up with the following which at least 
> got me to the point of valid SQL for base.
>
> Using the actual table names, so tablea becomes Wine and tableb 
> becomes Degustation and I needed another one MarketPrice.
>
> Couldn't get "Limit" to work, would love to know how one would use it.
>
> As Degustation table only has a single DeguID per WineID I got what I 
> needed, but in the MarketPrice table I am cheating with the avg(), 
> would really like to get latest/highest based on 
> MarketPrices.PurchaseDate. Could this be done by adding an Order_By 
> clause and Limit or ...???
>
> Anyhow this is pretty clause to what I needed and I probably can live 
> with it.
>
> Thanks again
> Werner
>
>
> SELECT
>     Wine.Year,
>     Wine.Name,
>     Wine.Appellation,
>     Wine.Region,
>     Wine.Country,
>     Wine.Variety,
>     Wine.Color,
>     Wine.Alcohol,
>     Wine.Producer,
>     (SELECT D_1.Total FROM Degustation as D_1 WHERE D_1.DeguID = 1 and 
> D_1.WineID = Wine.WineID) AS `Degu1 Total`,
>     (SELECT D_2.Total FROM Degustation as D_2 WHERE D_2.DeguID = 2 and 
> D_2.WineID = Wine.WineID) AS `Degu2 Total`,
>     (SELECT D_3.Total FROM Degustation as D_3 WHERE D_3.DeguID = 3 and 
> D_3.WineID = Wine.WineID) AS `Degu3 Total`,
>     (SELECT D_4.Total FROM Degustation as D_4 WHERE D_4.DeguID = 4 and 
> D_4.WineID = Wine.WineID) AS `Degu4 Total`,
>     (SELECT D_5.Total FROM Degustation as D_5 WHERE D_5.DeguID = 5 and 
> D_5.WineID = Wine.WineID) AS `Degu5 Total`,
>     (SELECT D_6.Total FROM Degustation as D_6 WHERE D_6.DeguID = 6 and 
> D_6.WineID = Wine.WineID) AS `Degu6 Total`,
>     (SELECT MAX(MP.MarketPrice) FROM MarketPrices as MP WHERE 
> MP.WineID = Wine.WineID) AS `Price`
> FROM
>     Wine, Degustation as DALL
> WHERE
>     Wine.WineID = DALL.WineID

Subquery must return only one result, otherwise whole thing will not work.
Reorder the result of MarketPrice query by either latest or highest and 
limit it. Try "LIMIT 0, 1" in your subquery.
It means starting from 1st result(0), display 1 result only.

cK

--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted