[SQL]right join, inner join en group by geven onjuist result

[van.der.schulting]

Onderstaand SQL-statement moet voor 12 maanden weergeven wat de waarde is van de verkochte dames-artikelen. In werkelijkheid is er in 4 maanden damesartikelen verkocht. De overige 8 maanden moeten weergegeven worden als een lege orderrij.

Onderstaand SQL-statement geeft alleen de 4 maanden terug en niet de overige 8 lege rijen:
SELECT SUM(orders_products.price) FROM orders_products RIGHT JOIN orders ON orders_products.order_id=orders.id
INNER JOIN products ON orders_products.product_id=products.id
INNER JOIN sexes ON sexes.id=products.sexe_id
WHERE sexes.title="Dames"
GROUP BY DATE_FORMAT(orders.created_at, "%M %Y")
ORDER BY orders_products.created_at

Toelichting:
SELECT SUM(orders_products.price)
Geeft de totale waarde van de orders per gegeven maand weer

RIGHT JOIN orders ON orders_products.order_id=orders.id
Zou ervoor moeten zorgen dat er een lege rij geretourneerd wordt, indien die maand geen damesartikelen zijn erkocht.

INNER JOIN products ON orders_products.product_id=products.id
INNER JOIN sexes ON sexes.id=products.sexe_id
Verzorgt de koppeling met de tabel de andere benodigde tabellen

WHERE sexes.title="Dames"
Als ik de WHERE clausule weghaal, dan krijg ik wel keurig de 12 maanden te zien, maar dan weet ik nog niet of er nou wel of geen damesartikelen zijn verkocht.

GROUP BY DATE_FORMAT(orders.created_at, "%M %Y")
Groepeert de orders per maand

Ik heb vanalles geprobeerd, maar ik kom er niet uit. Telkens krijg ik alleen de 4 maanden te zien dat er dames-artikelen zijn verkocht. Wie kan mij vertellen hoe ik de overige 8 maanden als 'lege rij' erbij kan weergeven in 1 SQL-statement?

[UltimateB]

Je kan niet echt verwachten dat er een sum gedaan wordt als er geen enkele data voldoet aan de group by van de maanden ervoor lijkt mij.

[mhaket]

Je probleem zit dat je een inner join doet met sexes en daarna een where op sexes.
Als je een lege rij hebt en dat joint met sexes komt daar natuurlijk niets uit. Dan de where er overheen levert op dat de regels niet getoond worden.

Je kunt het proberen met een left join op sexes maar er zijn volgens mij betere oplossingen.

[van.der.schulting]

mhaket schreef op zondag 16 november 2008 @ 20:24:
Je probleem zit dat je een inner join doet met sexes en daarna een where op sexes.
Als je een lege rij hebt en dat joint met sexes komt daar natuurlijk niets uit. Dan de where er overheen levert op dat de regels niet getoond worden.

Je kunt het proberen met een left join op sexes maar er zijn volgens mij betere oplossingen.

Als ik de WHERE weghaal, dan krijg ik wel keurig 12 rijen, maar ook de SUM van elke rij (maand). Als ik voor de lege maanden een SUM=0 terugkrijg is het goed, maar ik krijg de totale SUM van die maand.
Logisch, maar ik heb geen idee hoe ik SUM=0 kan terugkrijgen voor de lege maanden.

[Psychokiller]

Je WHERE zorgt er voor dat de regels wegvallen, immers NULL is niet gelijk aan "Dames" en dus wordt de rij niet meegenomen.

Verplaats je die where nou naar je JOIN gedeelte (ipv WHERE gebruik je dan AND), dan wordt er alleen gejoined indien die voorwaarde waar is.

[van.der.schulting]

Psychokiller schreef op zondag 16 november 2008 @ 22:33:
Je WHERE zorgt er voor dat de regels wegvallen, immers NULL is niet gelijk aan "Dames" en dus wordt de rij niet meegenomen.

Verplaats je die where nou naar je JOIN gedeelte (ipv WHERE gebruik je dan AND), dan wordt er alleen gejoined indien die voorwaarde waar is.

Die query:

code:

SELECT SUM(orders_products.price) FROM orders_products RIGHT JOIN orders ON orders_products.order_id=orders.id
INNER JOIN products ON orders_products.product_id=products.id
INNER JOIN sexes ON sexes.id=products.sexe_id
AND sexes.title="Dames"
GROUP BY DATE_FORMAT(orders.created_at, "%M %Y")
ORDER BY orders_products.created_at

resulteert weer in 4 rijen. De overige 8 rijen (waarvoor geldt SUM=0) worden niet weergegeven en die 8 rijen moeten (ook) weergegeven worden.

[Psychokiller]

Nu heb je nog een INNER JOIN op sexes, zoals mhaket al zei.
Volgens mij is het al genoeg als je daar een LEFT van maakt.

Ikzelf werk eigenlijk altijd vanaf links, waardoor ik met de hand op zoiets uitkom:

SELECT SUM(orders_products.price)
FROM orders
LEFT JOIN orders_products ON orders_products.order_id=orders.id
LEFT JOIN products ON orders_products.product_id=products.id
LEFT JOIN sexes ON sexes.id=products.sexe_id AND sexes.title="Dames"
GROUP BY DATE_FORMAT(orders.created_at, "%M %Y")
ORDER BY orders_products.created_at

De LEFT van Products op orders_products kan volgens mij een INNER zijn. Zo even geen idee of dat wat uitmaakt.

[van.der.schulting]

Psychokiller schreef op zondag 16 november 2008 @ 23:23:
Nu heb je nog een INNER JOIN op sexes, zoals mhaket al zei.
Volgens mij is het al genoeg als je daar een LEFT van maakt.

Ikzelf werk eigenlijk altijd vanaf links, waardoor ik met de hand op zoiets uitkom:

SELECT SUM(orders_products.price)
FROM orders
LEFT JOIN orders_products ON orders_products.order_id=orders.id
LEFT JOIN products ON orders_products.product_id=products.id
LEFT JOIN sexes ON sexes.id=products.sexe_id AND sexes.title="Dames"
GROUP BY DATE_FORMAT(orders.created_at, "%M %Y")
ORDER BY orders_products.created_at

De LEFT van Products op orders_products kan volgens mij een INNER zijn. Zo even geen idee of dat wat uitmaakt.

Vreemd genoeg slaat m'n MYSQL-server van deze query volledig op hol. Lees: de MYSQL-server verbruikt 100% van mijn CPU en mijn PC reageert nergens meer op. Na een minuut wachten heb ik maar gereboot.

Als ik van de products een INNER JOIN maak, dan krijg ik weer hetzelfde resultaat: 12 maanden, waarvan alle maanden een SUM > 0 hebben, terwijl er 8 rijen SUM = 0, want toen zijn er geen dames artikelen verkocht.

[van.der.schulting]

Ik heb de oplossing gevonden. Door mijn query als LEFT JOIN uit te voeren op de tabel orders_producs krijg ik de gewenste oplossing:

code:

SELECT total_price, orders_products.* 
FROM orders_products LEFT JOIN
(SELECT orders_products.*, SUM(orders_products.price) AS total_price FROM orders_products
INNER JOIN products ON orders_products.product_id=products.id
INNER JOIN sexes ON sexes.id=products.sexe_id AND sexes.title="Dames"
GROUP BY DATE_FORMAT(orders_products.created_at, "%M %Y")
) AS x ON DATE_FORMAT(x.created_at, "%M %Y") = DATE_FORMAT(orders_products.created_at, "%M %Y")
GROUP BY DATE_FORMAT(orders_products.created_at, '%M %Y') ORDER BY orders_products.created_at;

[winkbrace]

lol.. dat is de moeilijke oplossing ja (en niet netjes, bedenk dat je het wilt aanpassen over een jaar...)

Je zou beter deze post kunnen lezen:

mhaket schreef op zondag 16 november 2008 @ 20:24:
Je probleem zit dat je een inner join doet met sexes en daarna een where op sexes.
Als je een lege rij hebt en dat joint met sexes komt daar natuurlijk niets uit. Dan de where er overheen levert op dat de regels niet getoond worden.

Je kunt het proberen met een left join op sexes maar er zijn volgens mij betere oplossingen.

kleine toevoeging: left join op sexes and sexes.title = 'Dames'

Een andere oplossing is nog alleen tellen wat je wilt tellen mbv een CASE in je SELECT, maar waarschijnlijk is dat niet wat je uiteindelijk wilt.

SQL:

select sum(case when sexes.title = 'Dames' then orders_products.price else null end)

[ Voor 4% gewijzigd door winkbrace op 18-11-2008 13:38 ]

[MBV]

Ik zou dat sterk afraden. Ik heb heel wat van dit soort queries gezien:

SQL:

SELECT artikelnummer,
  filiaal_1,
  filiaal_2,
  filiaal_3,
  filiaal_4,
  ...
FROM (SELECT
  sum(case when filiaalid = 1 then totaalprijs else 0 end) filiaal_1,
  sum(case when filiaalid = 2 then totaalprijs else 0 end) filiaal_2,
  sum(case when filiaalid = 3 then totaalprijs else 0 end) filiaal_3,
  sum(case when filiaalid = 4 then totaalprijs else 0 end) filiaal_4,
  ...
)
....

Dat lijkt heel leuk met 8 filialen, maar 5 jaar later zijn het er een stuk of 25 voor het meest relevante bedrijfsonderdeel, en dan wordt je er nogal sacherijnig van. Bij booleans kan het wel een oplossing zijn.

offtopic:
de FROM SELECT heeft in sommige rapporten een functie i.v.m. een UNION
Oh ja, met de vriendelijke groeten van de vorige developer, 't wasn't me

[ Voor 10% gewijzigd door MBV op 18-11-2008 14:53 ]

[ACM]

van.der.schulting schreef op maandag 17 november 2008 @ 15:22:
Ik heb de oplossing gevonden. Door mijn query als LEFT JOIN uit te voeren op de tabel orders_producs krijg ik de gewenste oplossing:

Je query is in principe nog steeds fout. Een relationele database kan domweg geen data verzinnen die er niet is.

Als je de garantie wilt dat je alle maanden (of andere bereiken) in een periode hebt moet je een (virtuele) tabel maken met die maanden er in en daar tegen 'outer joinen'. In jouw geval is er blijkbaar wel elke maand wat verkocht volgens die order_products-tabel, maar als er een maandje had gemist, of als je per week of per dag de gegevens wilde weten had je vast weer iets gemist. Afgezien daarvan is jouw query waarschijnlijk niet al te vlot gezien het feit dat je geen index kan gebruiken voor jouw condities met functies erin op de datum...

Zoiets zou dan wel werken:

SQL:

SELECT m.month, SUM(orders_products.price)
FROM months m 
LEFT JOIN (orders
    JOIN orders_products ON orders_products.order_id=orders.id
    JOIN products ON orders_products.product_id=products.id
    JOIN sexes ON sexes.id=products.sexe_id AND sexes.title="Dames"
) ON orders.created_at BETWEEN m.startdate AND m.enddate
WHERE m.month BETWEEN '2008-01' AND '2008-12'
GROUP BY m.month

Let vooral op de geneste left join met daarbinnen inner joins. Op die manier dwing je af dat alle orders die uiteindelijk meegeteld worden ook echt aan je voorwaarden voldoen. Ik heb die between opgenomen omdat dat waarschijnlijk de beste manier is om indexen te kunnen gebruiken over de orders-tabel.

De varianten met meerdere losse left join's zijn fout omdat ze uiteindelijk wel ergens een van de tabellen weglaten en je uiteindelijk toch de totale som krijgt van de verkochte producten. De varianten met allemaal inner joins (al dan niet impliciet door een where-clause) zijn dan weer fout omdat je afdwingt dat er van elke tabel iets moet zijn en je dus lege maanden kwijtraakt.

Als je geen zin hebt om een months-tabel aan te maken, dan kan je die in jouw geval simuleren met een subselect, met zoiets:

SQL:

FROM (SELECT DATE_FORMAT(created_at, '%Y %M') as month,  
       MIN(created_at) as startdatum, MAX(created_at) as enddatum
    FROM orders
    WHERE created_at BETWEEN '2008-01-01' AND '2008-12-31'
) AS m 
LEFT JOIN (orders

Ik heb de queries niet getest, het gaat vooral om de ideeen erachter.