[sql] outer left join, group by, distinct en coalesce

[Dennis]

Ik heb een heel ingewikkelde query met een hoop statements.

Er zijn vier tabellen.

• Order
• ProductOrder
• ProductVersion
• QuantityDiscount

Order bevat orders. ProductVersion bevat de producten. ProductOrder is een koppeling tussen deze twee en heeft onder andere een hoeveelheid (hoeveel keer een bepaald product is besteld in een order).

Tot dan gaat alles goed. Maar nu hebben we iets leuks: een tabel QuantityDiscount die een korting op een bepaald product geeft zodra deze een x-aantal keer besteld is. Dus bijvoorbeeld als ik 10 keer een iPod bestel dan krijg ik 5% korting per iPod. Uiteraard niet over de gehele order.

De QuantityDiscount tabel ziet er als volgt uit:

code:

ProductVersionId    int     4
Border              int     4
Factor              decimal 9

Ook het berekenen van de discount gaat op zich prima met de volgende query:

code:

SELECT 
    DISTINCT(o.Id),
    o.TrackingKey,
    SUM(COALESCE(qd.Factor * (po.Quantity * pv.Price), po.Quantity * pv.Price)) AS SubTotal
FROM
    [Order] AS o
INNER JOIN ProductOrder AS po
    ON po.OrderId = o.Id
INNER JOIN ProductVersion AS pv
    ON pv.Id = po.ProductVersiontId
LEFT OUTER JOIN QuantityDiscount AS qd
    ON qd.ProductVersionId = pv.Id AND po.Quantity >= qd.Border
GROUP BY
    o.Id, o.TrackingKey

Zoals je ziet is mijn bedoeling om alle orders terug te krijgen met daarbij het totaalbedrag van die order. Op zich gaat het goed met bovenstaande query, op één uitzondering na, namelijk als er meerdere QuantityDiscount rijen zijn voor één ProductVersionId: als ik 20 producten bestel en er zijn discounts voor vanaf 10 en 20 producten, dan moet hij die van 20 nemen. Momenteel neemt hij echter beide rijen en telt de discount op (!) (bijvoorbeeld 0,3 + 0,5 betekent dat je 80% moet betalen, terwijl je dus bijvoorbeeld maar 30% hoeft te betalen).

Hoe is dit op te lossen .

Een order by-clausule voor de "left outer join qd.Border" is niet mogelijk, omdat het veld dan moet worden opgenomen in de SELECT. Vervolgens moet, omdat er een SUM wordt gebruikt, deze ook bij de GROUP BY worden opgenomen en dan krijg je 2 rijen terug zodra er twee QuantityDiscount rijen zijn voor één ProductVersionId.

Om dezelfde reden is ook een GROUP BY niet mogelijk.

MAX werkt ook niet omdat niet altijd de hoogste waarde moet worden geretourneerd.

Het is een klein beetje ingewikkeld en alle SQL guru's hier hebben ook geen idee hoe je dit nou moet oplossen, dus hopelijk heeft iemand hier een geniale ingeving .

Alvast bedankt.

[Verwijderd]

waarom zeg je dat max niet werkt? ik denk dat het een beetje lullig is voor je klant als je niet de maximale discount rekent.

[jvdmeer]

Ik weet niet met welk DBMS je werkt, maar je kan proberen om te joinen met een sub-query. op die manier moet 't lukken.
Anders kom ik je morgen nog wel tegemoet met een voorbeeld.

[Sijmen]

Verwijderd schreef op vrijdag 01 april 2005 @ 19:51:
waarom zeg je dat max niet werkt? ik denk dat het een beetje lullig is voor je klant als je niet de maximale discount rekent.

Er wordt een bepaalde korting gerekend vanaf een bepaalde hoeveelheid. Vanaf 10 eenheden heb je een bepaalde korting, en vanaf 20 eenheden een nog hogere korting.

jvdmeer schreef op zaterdag 02 april 2005 @ 09:41:
Ik weet niet met welk DBMS je werkt, maar je kan proberen om te joinen met een sub-query. op die manier moet 't lukken.
Anders kom ik je morgen nog wel tegemoet met een voorbeeld.

Het gaat om SQL Server.

[jvdmeer]

Nou, vooruit dan:
Dit is de juiste query: (ik heb 'm in een test-db getest en bij werkt hij goed)

Je zal alleen de tabelnamen en veldnamen goed moeten invullen.

SQL:

select * from 
  (
  select oa.artikel, oa.[order], oa.aantal, coalesce(max (korting),0) as MaxKorting
    from [1024493orderartikel] oa
    left join [1024493korting] k 
      on oa.artikel=k.artikel and k.aantal<=oa.aantal 
    group by oa.artikel, oa.[order], oa.aantal
  ) oak
  join [1024493artikel] art on art.id=oak.artikel
  join [1024493order] ord on ord.id=oak.[order]

[Dennis]

Alvast bedankt jvdmeer, ik heb nu nog weekend gelukkig maar ik zal hem morgenochtend uitproberen en het resultaat hier posten.

[Dennis]

Nou ik heb hem even aangepast aan onze tabellen en velden en moet zeggen dat hij inderdaad redelijk werkt. Jouw oplossing pakte nog wel alle producten van een order maar dat heb ik uiteraard even aangepast. Hij is nu als volgt:

code:

SELECT 
    oak.OrderId, 
    ord.TrackingKey,
    SUM(art.Price * oak.MaxKorting * oak.Quantity) AS SubTotal
FROM 
    (
    SELECT 
        oa.ProductVersiontId, 
        oa.OrderId, 
        oa.Quantity, 
        COALESCE(MIN(Factor), 1,0) AS MaxKorting
    FROM 
        [ProductOrder] oa
    LEFT JOIN [QuantityDiscount] k 
        ON oa.ProductVersiontId = k.ProductVersionId 
        AND k.Border <= oa.Quantity 
    GROUP BY 
        oa.ProductVersiontId, 
        oa.OrderId, 
        oa.Quantity
    ) oak
JOIN [ProductVersion] art 
    ON art.Id = oak.ProductVersiontId
JOIN [Order] ord 
    ON ord.Id = oak.OrderId
GROUP BY
    oak.OrderId,
    ord.TrackingKey

De MAX-korting heb ik aangepast naar MIN-factor. Hij neemt nu dus de goedkoopste oplossing voor de klant. Op zich is dat logisch, want als je meer producten besteld krijg je altijd meer korting. Toch zou het beter zijn om dat op te lossen, maar het is erg lastig om dat te wijzigen, want dan zou je eventueel weer een subquery erin moeten stoppen.

Heel erg bedankt jvdmeer!