[sql] dubbele join: foutje van mij of bug?

[MisterData]

Goed, ik heb dus een website, en daarop kan op zowel de frontpage als in het forum gepost worden. Nou wil ik wel es een top100 maken (als ik ooit op de 100 bezoekers kom ) dus ik denk, ik gebruik gewoon het volgende:

SQL:

SELECT users.id,users.username,count(posts.id) as postcount,count(reactions.id)
 as reactcount FROM users LEFT JOIN posts ON posts.poster=users.id LEFT JOIN 
reactions ON reactions.poster=users.id GROUP BY users.id ORDER BY postcount 
DESC

Niet dus. Wat er nu gebeurt is dat ik een hele rare uitkomst krijg: ik bijvoorbeeld heb op de site 237 forum posts, en 80 frontpage posts, maar de top100.php zegt doodleuk dat ik 18960 reacties op de FP heb en 18960 forumposts Ik snap dus niet waar MySQL die 18960 vandaan haalt. Na wat rekenen blijkt het dus 237*80 te zijn, dus op de een of andere manier vermenigvuldigd MySQL de twee counts Ben ik nou gek of heeft MySQL kuren?

[ Voor 2% gewijzigd door MisterData op 19-12-2002 21:03 . Reden: layout f*cked up ]

[whoami]

Allereerst moet je groeperen op alle velden die in je select-list staan en geen aggregated functie zijn. Je moet dus groupen op zowel user.id en users.username

[MisterData]

whoami schreef op 19 december 2002 @ 21:25:
Allereerst moet je groeperen op alle velden die in je select-list staan en geen aggregated functie zijn. Je moet dus groupen op zowel user.id en users.username

OK, dat heb ik nu aangepast, maar er is geen verschil Ik heb al een INNER JOIN in plaats van een LEFT JOIN geprobeerd, maar dan krijg ik vanzelfsprekend alleen users terug die iets hebben gepost (en lost het probleem ook niet op)

[whoami]

Herschrijf de query eens als volgt:
(* whoami is niet zo thuis in die JOIN - syntax)

code:

SELECT users.username, 
             count(posts.id) as postcount,
             count(reactions.id) as reactcount
FROM    users, posts, reactions
WHERE users.id = posts.poster
AND      reactions.poster = users.id
GROUP BY users.username

[MisterData]

ToMmY 19120 19120

Helaas Ook jouw query doet het niet goed

[Apollo_Futurae]

kijk eens goed naar het FROM-gedeelte van je query:
je joint eerst users met posts (zoals bedoeld) en vervolgens join je dit resultaat nog eens met reactions. mysql vormt dus het cartesisch product van je tussenresultaat en de reactions tabel; daardoor krijg je ook precies 80*237 rows.
het is me niet helemaal duidelijk wat je wel wilt: moeten de gebruikers geordend worden op het aantal posts en reacties samen? Of op de grootste van de twee counts?

[whoami]

Hmm, Apollo_Futurae heeft misschien wel een punt ivm dat cartesiaans product.
Moet je posts en reactions ook niet met elkaar joinen?

[MisterData]

whoami schreef op 19 December 2002 @ 22:30:
Hmm, Apollo_Futurae heeft misschien wel een punt ivm dat cartesiaans product.
Moet je posts en reactions ook niet met elkaar joinen?

Hmm dat kan ik eens proberen. Het is de bedoeling dat de postcount op het forum opgeteld wordt bij de fp-posts, maar ik wil ze alsnog allebei los in een tabelletje kunnen zetten (dus zoiets als "User xxx heeft xxx fp-posts en xxx forum-posts (totaaal xxx) en staat daarmee op de xx'e plaats) Is er niemand die weet hoe ik dit moet doen?

[ Voor 6% gewijzigd door MisterData op 20-12-2002 12:46 ]

[Unipuma]

Als ik het goed begrijp wil je een lijst met users, en daarachter het aantal posts en het aantal reacties dat ze gedaan hebben? Je gebruikt de LeftJoin precies verkeerd om. Probeer eens:

SELECT users.id,users.username,count(posts.id) as postcount,count(reactions.id) as reactcount
FROM users
LEFT JOIN posts ON users.id = posts.poster
LEFT JOIN reactions ON users.id = reactions.poster
GROUP BY users.id
ORDER BY postcount DESC

LeftJoin betekend dat je alles aan de linkerkant van de vergelijking wilt zien en alleen de overeenkomstige aan de rechterkant. En je wilt juist alle userId's zien.

[MisterData]

Helaas.....

1 ToMmY 19120 19120

Nog steeds hetzelfde dus....

[whoami]

Doe misschien eerst eens een eenvoudige query, waar je bv enkel het aantal posts ophaalt en nog geen rekening houdt met het aantal reacties.
Kijk eens wat je dan terugkrijgt.

[LuCarD]

Misschien ben ik gek maarhe...

Post en Reacties hebben in principe niks met elkaar te maken. Dus die kan je ook nooit om deze manier joinen...

Tenministe niet met MySQL.

[MisterData]

whoami schreef op 20 december 2002 @ 15:13:
Doe misschien eerst eens een eenvoudige query, waar je bv enkel het aantal posts ophaalt en nog geen rekening houdt met het aantal reacties.
Kijk eens wat je dan terugkrijgt.

Als ik dit weglaat:

SQL:

LEFT JOIN reactions ON reactions.poster=users.id

dan gaat het gewoon goed

LuCard: Misschien ben ik gek maarhe...

Post en Reacties hebben in principe niks met elkaar te maken. Dus die kan je ook nooit om deze manier joinen...

Tenministe niet met MySQL.

Jij bent gek Dat kan namelijk wel, alleen vermenigvuldigd MySQL twee getalletjes, terwijl ik dat niet wil

[ Voor 30% gewijzigd door MisterData op 20-12-2002 17:05 ]

[Unipuma]

Hmm, mischien dat je een trucje kunt toepassen. Beetje afhankelijk van je DB volgens mij of het ondersteunt wordt, maar probeer eens het volgende:

SELECT users.id,users.username,count(DISTINCT posts.id) as postcount,count(DISTINCT reactions.id) as reactcount
FROM users
LEFT JOIN posts ON users.id = posts.poster
LEFT JOIN reactions ON users.id = reactions.poster
GROUP BY users.id
ORDER BY postcount DESC

Ik ga er hier vanuit dat de posts.id en reactions.id unieke velden zijn in die twee tabellen. Het toevoegen van DISTINCT zou moeten zorgen dat er geen dubbeltellingen meer plaatsvinden. Hij moet echter wel DISTINCT op kolom niveau toestaan, en niet alleen op record niveau. (Weet niet of MySQL dat toelaat)

[MisterData]

Unipuma schreef op 20 December 2002 @ 18:37:
Hmm, mischien dat je een trucje kunt toepassen. Beetje afhankelijk van je DB volgens mij of het ondersteunt wordt, maar probeer eens het volgende:

SELECT users.id,users.username,count(DISTINCT posts.id) as postcount,count(DISTINCT reactions.id) as reactcount
FROM users
LEFT JOIN posts ON users.id = posts.poster
LEFT JOIN reactions ON users.id = reactions.poster
GROUP BY users.id
ORDER BY postcount DESC

Ik ga er hier vanuit dat de posts.id en reactions.id unieke velden zijn in die twee tabellen. Het toevoegen van DISTINCT zou moeten zorgen dat er geen dubbeltellingen meer plaatsvinden. Hij moet echter wel DISTINCT op kolom niveau toestaan, en niet alleen op record niveau. (Weet niet of MySQL dat toelaat)

Je bent een held