Date: prev next · Thread: first prev next last
2012 Archives by date, by thread · List index

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

(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`
    Wine, Degustation as DALL
    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.


For unsubscribe instructions e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.