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

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

 * Unknown - detected
 * English

 * English

 <javascript:void(0);>


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