Problem mit sum() / JOIN / IFNULL - mehr tabellen

[hausl]

Hallo!

Ich komm gleich zur Sache.. ich habe (die relevanten) Tabellen...

tabuser:
- nickname

tabtipps:
- nickname
- punkte

tabzwtipps:
- nickname
- punkte

Der nickname ist in diesem Fall die ID, puntke ist TINYINT... Jeder User kann in jeder tabelle mehr Tipps haben - muss aber nicht (= NULL möglich!)

Ich brauche nun ein Gesamtranking über beide Tabellen aufsummiert nach User...

Ich bin schon soweit das es eigenltich funktioniert - siehe unten - aber der erst Wert leider nicht.. der muss lt. DB 30 sein!!

Wenn ich statt IFNULL das coalesce nehme kommt das gleiche raus..

SELECT u.Nickname, sum( IFNULL(t.Punkte, 0) + IFNULL(z.Punkte, 0) ) AS UserSumme
FROM tabuser u
LEFT JOIN tabtipps t
  ON u.Nickname = t.Nickname
LEFT JOIN tabzwtipps z
  ON u.Nickname = z.Nickname
GROUP BY u.Nickname
ORDER BY UserSumme DESC, Nickname ASC 

Das kommt derzeit raus - wie gesagt der erste muss 30 sein - die anderen stimmen!

Nickname  UserSumme
Hiasi        178
Franzi        13
Hans           8
Karli          7
Seppi          5
admin          0

Wo ist der Hund begraben.. ??

Danke für jeden Tipp - falls wer den SQL create-dump braucht kann ich den gerne hier einstellen..

Juergen

[mimomamu]

FROM tabuser u 

Jeder Benutzer bekommt einen Datensatz im Zwischenergebnis

LEFT JOIN tabtipps t
  ON u.Nickname = t.Nickname 

Aus dem einen Datensatz pro User werden jetzt so viele Datensätze, wie der User tabtipps hat. Wenn jeder User 3 tabtipps hat, dann hast du jetzt drei mal so viele Datensätze im Zwischenergebnis.

LEFT JOIN tabzwtipps z
  ON u.Nickname = z.Nickname 

Zu jedem Zwischenergbnis wird jetzt geschaut, ob der im Zwischenergebnis erwähnte User auch tabzwtipps hat. Wenn jeder User 4 tabzwtipps hat, und jeder User 3 mal im vorherigen Zwischenergebnis auftaucht, dann taucht jeder User nach diesem JOIN (jeder mit jedem) 12 mal im neuen Zwischenergbnis auf.

GROUP BY u.Nickname 

Jetzt erst wird anhand des Users gruppiert. In das Endergbnis fließen also 3 * 4 = 12 Datensätze pro User ein, obwohl du eigentlich 3 + 4 = 7 haben willst.

[hausl]

Und wie mache ich es richtig ??

[mimomamu]

Ohne Gewähr:

SELECT
  u.Nickname,
  (SELECT SUM(Punkte) FROM tabtipps AS t WHERE t.Nickname = u.Nickname) + 
  (SELECT SUM(Punkte) FROM tabzwtipps AS tz WHERE tz.Nickname = u.Nickname) AS total
FROM tabuser AS u
ORDER BY total DESC, Nickname ASC

Sieht auf den ersten Blick wie ein Performance-Albtraum aus, aber vieleicht ist dein DBMS ja intelligent genug, nicht für jeden User zwei Unterabfragen rauszuhauen.

[hausl]

Ähm.. aktuelles MySQL .. ach das wird schon gehen.. alternative wäre 2 Abfragen in ein array rein und sortieren (PHP) geht auch nicht fixer..

Dein Statement läuft.... und bringt...

"Nickname";"total"
"Hiasi";"31"
"Hans";"13"
"Karli";"7"
"Seppi";"5"
"admin";NULL
"Franzi";NULL

Muss schauen.. weil mein bisheriges bringt...

"Nickname";"UserSumme"
"Hiasi";"180"
"Karli";"21"
"Seppi";"15"
"Franzi";"13"
"Hans";"13"
"admin";"0"

juergen

[hausl]

Nachtrag..

Muss mir deins noch ansehen..stimmt nicht ganz.. bei tabzwtipps ist derzeit nur einmal punkte (2 Pkt.) für hiasi drinnen, der rest ist...

$sql="SELECT Nickname, SUM(Punkte) AS UserSumme
      FROM tabtipps
      GROUP BY Nickname
      ORDER BY UserSumme DESC, Nickname ASC";


ergibt:

Hiasi 29 
Franzi 13 
Hans 13 
Karli 7 
Seppi 5

Muss morgen nochmals schaun...

[hausl]

Ist erledigt, ich hab es nun soweit das es scheint zu laufen:

SELECT u.Nickname, IFNULL(t.Punkte, 0) + IFNULL(z.Punkte, 0) UserSumme
FROM tabuser u
LEFT JOIN (
	SELECT Nickname, SUM(Punkte) Punkte
	FROM tabtipps
	GROUP BY Nickname
) t
  ON u.Nickname = t.Nickname
LEFT JOIN (
	SELECT Nickname, SUM(Punkte) Punkte
	FROM tabzwtipps
	GROUP BY Nickname
) z
  ON u.Nickname = z.Nickname
GROUP BY u.Nickname
ORDER BY UserSumme DESC, Nickname ASC