[SQL] Probleem met joins en SUM

[Eelke Spaak]

Ik maak gebruik van een datastructuur waaruit ik alle orders wil selecteren. Nu is het zo dat aan elke order een aantal deliveries gekoppeld zit, en aan die deliveries weer een lijst met products. Ik wil bij mijn order-SELECT ook de totale kosten van de order laten zien. Nou gaat dat prima als ik de volgende query gebruik:

SQL:

SELECT o.orderId SUM(p.cost) AS totalCost

FROM orders AS o

LEFT JOIN deliveries AS d ON (d.orderId = o.orderId)
LEFT JOIN deliveries_products AS dp ON (dp.deliveryId = d.deliveryId)
LEFT JOIN products AS p ON (p.productId = dp.productId)

GROUP BY o.orderId

Alleen zit hier slechts de prijs van de producten bij. Ik wil ook de verzendkosten erbij rekenen. Ik krijg maar geen goede query bedacht om die er ook bij te betrekken.

Ik heb een aantal tabellen, waarvan ik even de relevante velden post:

orders
orderId

deliveries
deliveryId
postalCodeNumber

deliveries_products
deliveryId
productId

products
productId
cost

deliveryCharge_postalCodes
pcRangeStart
pcRangeEnd
deliveryCost

Ik heb al iets met subquerys geprobeerd, en ook het volgende:

SQL:

SELECT o.orderId (SUM(p.cost) + SUM(dcp.deliveryCost)) AS totalCost

FROM orders AS o

LEFT JOIN deliveries AS d ON (d.orderId = o.orderId)
LEFT JOIN deliveries_products AS dp ON (dp.deliveryId = d.deliveryId)
LEFT JOIN products AS p ON (p.productId = dp.productId)

LEFT JOIN deliveryCharge_postalCodes AS dcp
ON (d.postalCodeNumber > dcp.pcRangeStart - 1
 AND d.postalCodeNumber < dcp.pcRangeEnd + 1)

GROUP BY o.orderId

maar dat werkt allemaal niet. In de deliveryCharge_postalCodes tabel staat momenteel 1 record: aan PC's 1-9999 moet 3,50 worden gekoppeld. Ik krijg bij bovenstaande query 17,50 terug als totalCost bij een order waarbij de producten gratis zijn en er 2 deliveries in het spel zijn.

Iemand enige suggesties? Ik gebruik overigens MS SQL Server 2000.

[ Voor 2% gewijzigd door Eelke Spaak op 14-08-2004 14:44 . Reden: anti layout verneuk ]

[Gert]

Waarom al die left joins? Als er een delivery is neem ik aan dat er niet null producten geleverd worden dus zou dat een inner join moeten zijn.
Verder geeft een where postalcodenumber beween 1 and 2 duidelijker weer waar het over gaat.

Sommeren over een kolom met een NULL waarde gaat ook fout dus zorg dat dat niet gebeurd.

[ Voor 4% gewijzigd door Gert op 14-08-2004 14:54 ]

[Verwijderd]

Gert schreef op 14 augustus 2004 @ 14:53:
Waarom al die left joins? Als er een delivery is neem ik aan dat er niet null producten geleverd worden dus zou dat een inner join moeten zijn.

Dan zou ik gewoon een left join aanhouden, tenzij je ook nog eens een inner join in een left join wilt stoppen.

[Eelke Spaak]

Gert schreef op 14 augustus 2004 @ 14:53:
Waarom al die left joins? Als er een delivery is neem ik aan dat er niet null producten geleverd worden dus zou dat een inner join moeten zijn.
Verder geeft een where postalcodenumber beween 1 and 2 duidelijker weer waar het over gaat.

Sommeren over een kolom met een NULL waarde gaat ook fout dus zorg dat dat niet gebeurd.

Ik kan denk ik idd ook de products inner joinen, maar dat is nu niet waar het om gaat. Zo werkt het; misschien dat ik daar later nog naar kijk.

[justmental]

Als je in deliveryCharge_postalCodes altijd maar 1 record moet hebben dan moet je daar op groupen en in je select dan SUM(p.cost) + dcp.deliveryCost gebruiken.

[Eelke Spaak]

justmental schreef op 14 augustus 2004 @ 17:52:
Als je in deliveryCharge_postalCodes altijd maar 1 record moet hebben dan moet je daar op groupen en in je select dan SUM(p.cost) + dcp.deliveryCost gebruiken.

Nee, dan was het inderdaad makelijk geweest, maar dat is nú alleen zo. Wanneer de applicatie in gebruik wordt genomen moeten er verschillende verzendkosten aan verschillende postcodes worden gekoppeld (bv. randstad 2 euro, daarbuiten 3 euro).

[justmental]

Vladimir G. schreef op 14 augustus 2004 @ 19:20:
[...]

Nee, dan was het inderdaad makelijk geweest, maar dat is nú alleen zo. Wanneer de applicatie in gebruik wordt genomen moeten er verschillende verzendkosten aan verschillende postcodes worden gekoppeld (bv. randstad 2 euro, daarbuiten 3 euro).

Ik bedoel 1 record per order, is dat altijd zo?

[Eelke Spaak]

justmental schreef op 14 augustus 2004 @ 19:42:
[...]

Ik bedoel 1 record per order, is dat altijd zo?

Nee, er is 1 record uit deliveryCharge_postalCodes gekoppeld aan één delivery. Eén order kan namelijk deliveries bevatten die naar heel verschillende postcodes gaan.

Ik heb even je suggestie geprobeerd om te kijken wat het doet, maar dan krijg ik 3,50 terug als verzendkosten bij de order met 2 deliveries (die dus 7 euro zou moeten geven).