SQL triple join probleem

[Tharulerz]

Beste mede-tweakers,

Een vraagje waar mijn oververhitte hersenpan even niet uitgeraakt, aldoch het vrij simpel is:

Gegeven bijvoorbeeld een soort van webshop (basic voorbeeld, maar context maakt eigenlijk niet uit), waar mensen producten kunnen kopen, maar ook klachten kunnen rapporteren.

3 tabellen: users, producten, klachten

Nu zou ik graag een query hebben die het volgende teruggeeft:

User1 | aantalproductengekocht | aantalklachtengedaan
User2 | aantalproductengekocht | aantalklachtengedaan
User3 | aantalproductengekocht | aantalklachtengedaan

Ik kan perfect kolom 1 en 2 met 1 query ophalen

SQL:

SELECT username, count(productid) FROM users join products on users.id = products.userid

en kolom 1 en 3 met een andere query, maar ik vroeg me af of het ook in 1 query kon (applicatie is groter, wil niet die dingen gaan koppelen in php, etc).

Het probleem is dat als ik beide ga combineren, mysql ze ook op elkaar joined (dus 2 met 3), waardoor je een factor records teveel krijgt.

Nogmaals, het voorbeeld is fictief, dus ook geen vragen waarom ik in godsnaam producten en klachten in dezelfde query zou willen ophalen.

Iemand een suggestie?

[Janoz]

Toch is het handiger om dit gewoon met twee losse queries te doen. Het punt is dat producten en klachten zelf onderling helemaal geen koppeling hebben. Voordat er gecount kan worden heb je dus al een cartesisch product te pakken.

[Avalaxy]

SQL:

SELECT users.username, count(products.productid), count(klachten.klachtenid)
FROM users 
JOIN products 
ON users.id = products.userid
JOIN klachten
ON products.userid = klachten.userid

Zoiets? Of begrijp ik je vraag verkeerd?

[Tharulerz]

Avalaxy schreef op maandag 12 juli 2010 @ 15:55:

SQL:

SELECT users.username, count(products.productid), count(klachten.klachtenid)
FROM users 
JOIN products 
ON users.id = products.userid
JOIN klachten
ON products.userid = klachten.userid

Zoiets? Of begrijp ik je vraag verkeerd?

Dat is dus de oplossing die niet werkt, aangezien je dan een cartesisch product teveel doet (producten x klachten)

Janoz, ik weet dat er geen koppeling is met producten en klachten (daarom dat het een fictief voorbeeld is), maar in de echte situatie liggen de 2 dingen dichter bij elkaar (maar niet dicht genoeg om ze in dezelfde tabel te steken).

Voorlopig zal ik het dan maar in php oplossen, alhoewel ik ergens hoop dat er nog een oplossing uit de lucht komt vallen (tenzij het uberhaupt technisch onmogelijk is?)

[GlowMouse]

Avalaxy schreef op maandag 12 juli 2010 @ 15:55:

SQL:

SELECT users.username, count(products.productid), count(klachten.klachtenid)
FROM users 
JOIN products 
ON users.id = products.userid
JOIN klachten
ON products.userid = klachten.userid

Zoiets? Of begrijp ik je vraag verkeerd?

Doe dan count distinct, want count(kolom) verschilt alleen van count(*) bij een nullable kolom.

[Motrax]

Avalaxy schreef op maandag 12 juli 2010 @ 15:55:

SQL:

SELECT users.username, count(products.productid), count(klachten.klachtenid)
FROM users 
JOIN products 
ON users.id = products.userid
JOIN klachten
ON products.userid = klachten.userid

Zoiets? Of begrijp ik je vraag verkeerd?

LEFT OUTER JOIN gebruiken en een GROUP BY userid, username toevoegen en en klaar is Tharulerz.

Janoz schreef op maandag 12 juli 2010 @ 15:55:
Toch is het handiger om dit gewoon met twee losse queries te doen. Het punt is dat producten en klachten zelf onderling helemaal geen koppeling hebben. Voordat er gecount kan worden heb je dus al een cartesisch product te pakken.

Waarom een carthesisch product? Als je 2 left outer joins start van uit users, en dan een count uitvoert kom je toch niet uit op een carthesisch product?


Ik weet dat het een voorbeeld is, maar de tabel 'producten' is niet juist. Ik zou daar een koppeltabel verwachten tussen users en producten. Klachten daarentegen kan weer wel. Maar goed, voorbeelden zijn voorbeelden

[Erhnam]

Je zou ook haakjes om een select kunnen doen waarbij je twee tabellen joint en dat select weer joinen aan de derde tabel. Wellicht ben je er zo?

[Janoz]

Als het in het echt toch dichter bij elkaar ligt, waarom maak je er dan een fictief voorbeeld van? Blijkbaar heb je de essentie van je probleem niet terug kunnen laten komen in je fictieve voorbeeld.

Maar voor je perceptie. Wat je je moet beseffen is dat een aggregerende functie (zoals count, sum, avg max enz enz) als doel heeft een deel van de kolomwaarden samen te nemen tot 1 kolom. Wanneer je dus bezig bent om een query te maken waarin je een aggregerende functie gebruikt zul je eerst een query moeten maken die een tabel oplevert met daarin alle records die je zou willen hebben. Probeer jij je nu maar eens voor te stellen hoe een tabel eruit moet zien waarin twee compleet niet gerelateerde items (producten, klachten) terug moeten komen, enkel omdat ze elk aan hetzelfde derde object gekoppeld zouden zijn (user).

Dat zal een tabel worden als:

user	product	klacht
jan	piefje	NULL
jan	pafje	NULL
jan	NULL	Dit niet
jan	NULL	Dat niet
jan	NULL	Zus niet
piet	piefje	NULL
piet	pafje	NULL
piet	NULL	Zo niet

[Tharulerz]

Motrax schreef op maandag 12 juli 2010 @ 16:04:
[...]
LEFT OUTER JOIN gebruiken en een GROUP BY userid, username toevoegen en en klaar is Tharulerz.


[...]
Waarom een carthesisch product? Als je 2 left outer joins start van uit users, en dan een count uitvoert kom je toch niet uit op een carthesisch product?


Ik weet dat het een voorbeeld is, maar de tabel 'producten' is niet juist. Ik zou daar een koppeltabel verwachten tussen users en producten. Klachten daarentegen kan weer wel. Maar goed, voorbeelden zijn voorbeelden

Nou dat had ik dus al geprobeerd (met 2 LEFT JOINS (outer heeft geen betekenis buiten odbc functionaliteit in mysql)), en je krijgt nog steeds een cartesisch product tussen producten en klachten.

En zoals ik zei, het is een voorbeeld, producten is in de echte applicatie iets unieks (elke record is unique), dus er is geen producttable mogelijk oid. De echte applicatie uitleggen was veel te ingewikkeld in vergelijking met een webshop voorbeeld.

[KabouterSuper]

Ik ben het met Janoz eens....gewoon twee queries van maken, en evt via een subquery de twee resultaten samenpakken:

code:

select username, sum(count_producten), sum(count_klachten)
from
(
select userid as id,count(1) as count_producten, 0 as count_klachten from producten
group by userid
union
select userid,0, count(1) from klachten
group by userid
) as tmp, users
where tmp.id=user.id
group by username

[Tharulerz]

Janoz schreef op maandag 12 juli 2010 @ 16:12:
Als het in het echt toch dichter bij elkaar ligt, waarom maak je er dan een fictief voorbeeld van? Blijkbaar heb je de essentie van je probleem niet terug kunnen laten komen in je fictieve voorbeeld.

Maar voor je perceptie. Wat je je moet beseffen is dat een aggregerende functie (zoals count, sum, avg max enz enz) als doel heeft een deel van de kolomwaarden samen te nemen tot 1 kolom. Wanneer je dus bezig bent om een query te maken waarin je een aggregerende functie gebruikt zul je eerst een query moeten maken die een tabel oplevert met daarin alle records die je zou willen hebben. Probeer jij je nu maar eens voor te stellen hoe een tabel eruit moet zien waarin twee compleet niet gerelateerde items (producten, klachten) terug moeten komen, enkel omdat ze elk aan hetzelfde derde object gekoppeld zouden zijn (user).

Dat zal een tabel worden als:
[...]

Je hebt gelijk dat het voorbeeld misschien kort door de bocht is, maar de applicatie uitleggen is nogal ingewikkeld voor meerdere redenen. (helaas)

Dat zou inderdaad een tabel worden die er zo uitziet, en die zegt misschien niet veel, maar ze zou wel men counts mogelijk maken

Ik bedoel maar, stel dat je 7 counts moet ophalen van een user (aantal bestellingen, aantal klachten, aantal betalingen, aantal facturatieadressen, je kan gerust wel aanvullen), dan moet je dus 7 queries doen en de rest in je application layer oplossen? Dat is eigenlijk de essentie.

[KabouterSuper]

Je hoeft het niet per se in je applicatielaag op te lossen, maar dat zou inderdaad wel het netste zijn. Met 7 counts is een join uit den boze.

[Janoz]

Tharulerz schreef op maandag 12 juli 2010 @ 16:16:
Ik bedoel maar, stel dat je 7 counts moet ophalen van een user (aantal bestellingen, aantal klachten, aantal betalingen, aantal facturatieadressen, je kan gerust wel aanvullen), dan moet je dus 7 queries doen en de rest in je application layer oplossen? Dat is eigenlijk de essentie.

Ja.

Bedenk trouwens dat een dergelijke enkele count functie razend snel kan zijn vanwege de te gebruiken index. De kans is groot dat de 7 losse queries een heel stuk sneller zijn dan 1 dik joinende superquery.

[HenroOnline]

SQL:

SELECT users.username, IfNull(p.productCount, 0) AS ProductCount, IfNull(k.klachtenCount, 0) AS KlachtenCount
FROM users
LEFT JOIN (SELECT products.userid, COUNT(*) AS productCount
       FROM products
           GROUP BY products.userid) p
  ON users.id = p.userid
LEFT JOIN (SELECT klachten.userid, COUNT(*) AS klachtenCount
       FROM klachten
       GROUP BY klachten.userid) k
  ON users.id = k.userid