[Chr!s]

Tag,
ich hab hier ein Problem mit einem MySQL-Query welcher sich über insgesamt 3 Tabellen erstreckt. Dabei will ich folgendes erreichen:
* gameID, gameName usw.. aus der angegebenen Kategorie auslesen
* den User mit der höchsten Punktzahl bei diesem Spiel auswählen

Mein Query sieht derzeit so aus:

SELECT
        gl.gameID, gl.gameName, gl.gamePath, gl.gamePicture,
        MAX(s.score) AS maxScore, s.userID, u.name AS username
FROM
        backend_gamesarea_gamelist AS gl
LEFT JOIN
        backend_gamesarea_scorelist AS s
ON
        s.gameID = gl.gameID
LEFT JOIN
        backend_usertable AS u
ON
        u.id = s.userID
WHERE
        gl.categoryID = '1'
GROUP BY
        gl.gameID
ORDER BY
        gl.gameID ASC

Ich bin schon relativ nahe dran an meinem gewünschten Ergebnis, bis auf eine Sache: Es wird die falsche userID ausgewählt. Denn in der Tabelle backend_gamesarea_scorelist steht der User mit der ID 3 bei der höchsten punktzahl, als Ergebnis bekomme ich allerdings den ersten User, den MySQL in der Tabelle findet.

Was ich also brauche ist noch irgendein Befehl oder eine Weise, wie ich die userID auswählen kann, die zu dem höchsten score (MAX(s.score)) gehört. Mein Kunde hat die MySQL Version 4.0.15, SubSelects werden aber erst ab 4.1 unterstützt, sonst hätte ich das Problem schon längst so gelöst.. Jemand ne Idee?

[WarrenFaith]

Was ich also brauche ist noch irgendein Befehl oder eine Weise, wie ich die userID auswählen kann, die zu dem höchsten score (MAX(s.score)) gehört.

Wenn ich das jetzt nicht falsch verstanden habe, dann brauchst du einfach ein ORDER BY maxScore, denn dann ist der User als erster dran, der auch die meiste Score hat...
Damit du dann aber auch nicht 100 Ergebnisse bekommst wenn du nur eins haben willst, verwende noch LIMIT 1

[Chr!s]

Zitat von WarrenFaith

Wenn ich das jetzt nicht falsch verstanden habe, dann brauchst du einfach ein ORDER BY maxScore, denn dann ist der User als erster dran, der auch die meiste Score hat...
Damit du dann aber auch nicht 100 Ergebnisse bekommst wenn du nur eins haben willst, verwende noch LIMIT 1

Naja ich les das in einer Schleife aus, ich bruach schon alle Spiele in der Kategorie. Das ORDER BY brachte aber leider nicht das gewünschte Ergebnis. Hier mein bisheriges Ergebnis mit meinem Query:

 gameID  gameName   	  gamePath   	                         gamePicture   	                       maxScore userID  username
1 	Bomberman 	./games/abenteuer/bomberman.swf 	./games/abenteuer/bomberman.gif 	NULL 	NULL 	NULL
2 	Streets of fire ./games/abenteuer/streetsoffire.swf 	./games/abenteuer/streetsoffire.gif 	9999 	1 	webmaster
3 	Indianajones 	./games/abenteuer/indianajones.swf 	./games/abenteuer/indianajones.gif 	NULL 	NULL 	NULL
4 	Megaman 	./games/abenteuer/megaman.swf 	        ./games/abenteuer/megaman.gif 	       NULL 	NULL 	NULL
5 	Links Rupee Madness ./games/abenteuer/zeldaruppee.swf 	./games/abenteuer/zeldaruppee.gif 	NULL 	NULL 	NULL

NULL kommt daher, da noch nicht genug DummyDaten existieren. Derzeit existieren nur 2 DummyDaten in der backend_gamesarea_scorelist, einmal
userID 3 mit score 9999 bei gameID 2 und
userID 1 mit score 123 bei gameID 2

Leider wird (wie oben ersichtlich) userID 1 gewählt, obwohl userID 3 richtig wäre..

[Chr!s]

Keine eine Lösung für mein Problem? Es eilt ein bisschen, und ich kriegs einfach nich hin =/

[$traight-$hoota]

wenns eilt nimm halt ein zweites query...
hab auch ka wie das ohne subselects möglich is.

[WarrenFaith]

Wir haben darüber auch grade gebrütet und es geht leider nicht, da er unter Where/On kein Max() erlaubt.
Chr!s wirds wohl auch mit 2 Querys machen müssen. Aber definitiv ein Grund, sein MySQL zu aktualisieren.

[Chr!s]

Wir haben darüber auch grade gebrütet und es geht leider nicht, da er unter Where/On kein Max() erlaubt.
Chr!s wirds wohl auch mit 2 Querys machen müssen. Aber definitiv ein Grund, sein MySQL zu aktualisieren.

Ist nicht meins, ist das vom Kunden.. Bei mir läuft mysql 5

Danke nochmal für die Hilfe, ich kriegs auch nicht anders als mit 2 Queries hin..

[Ben]

Dann soll der Kunde das halt aktualisieren.
Ich kann es bald nicht mehr hören .. "der Kunde" .. "das geht da nicht" .. ..

[MrNiceGuy]

@Ben: Der Kunde ist nunmal König. Das war schon immer so und das wird auch immer so bleiben. Und wenn der Kunde ein Hello-World-Script für 500 Euro kaufen will, dann soll er dies auch tun. Mehr als es dem Kunden anraten kann man nunmal nicht, wenn er aber auf gute Begründungen nicht eingeht, hat man als Programmierer halt das Nachsehen oder man sagt ihm dann, dass man das Projekt nicht annehmen kann/will und das Geld fließt in die Taschen eines anderen Entwicklers, dem es eben scheißegal ist, was der Kunde für ne Version benutzen will.

EDIT: Zum Thema selbst fällt mir gerade ein, dass ich das selbe Problem bei meinem Forum hatte: Eine Tabelle für die Foren, eine für die Threads und eine für die Posts. Glaubst du es hat funktioniert in einem (!!) Query (ohne Subqueries, die gab es damals noch nicht), eine Foren-Übersicht auszulesen, in der dann der letzte Post mit User per Join angezeigt wurde? Es war schlichtweg nicht möglich. Man bekam immer mal irgendeinen Post / User heraus, aber nie den neuesten (komischerweise war es aber auch nie der Selbe!?).

Ich musste dann umsteigen und temporäre Spalten für DATETIME, PostID, ThreadID und UserID in den anderen Tabellen anlegen

[JumperII]

Hallo,

also die Query arbeitet völlig korrekt. Ich vermute mal, Du suchst so eine Abfrage:

SELECT 
        gl.gameID, 
        gl.gameName,
        gl.gamePath, 
        gl.gamePicture, 
        sa.score as maxScore, 
        sa.userID, 
        u.name as username
FROM
        backend_gamesarea_gamelist AS gl
LEFT JOIN
	(
	SELECT
 		s.userID, 
                 s.gameID, 
                 s.score
	FROM
		(
		SELECT 
                         MAX( score) as maxscore, 
                         gameID 
                 FROM
                         backend_gamesarea_scorelist 
                 GROUP BY 
                         gameID
		) AS a,
	        backend_gamesarea_scorelist AS s
	WHERE
		s.score = a.maxscore AND s.gameID = a.gameID
	) AS sa
ON
	sa.gameID = gl.gameID
LEFT JOIN
        backend_usertable AS u
ON
        u.id = sa.userID
WHERE
        gl.categoryID = '1'
GROUP BY
        gl.gameID
ORDER BY
        gl.gameID ASC

Gruß,
Jumper, the II.

[WarrenFaith]

Zitat von Chr!s

Mein Kunde hat die MySQL Version 4.0.15, SubSelects werden aber erst ab 4.1 unterstützt, sonst hätte ich das Problem schon längst so gelöst..

Zitat von JumperII

Ich vermute mal, Du suchst so eine Abfrage:

Ohne Subselects und schon wäre es schön

[JumperII]

Ups ... mir entgangen, dass es erst sei t 4.1 drin ist ... naja, dann empfehle ich, die Anwendung umzuprogrammieren. Einfach eine zusätzliche Tabelle mit dem highscore eines spieles mit verwalten.
Alternativ natürlich n Abfragen, für n Spiele ....

Ansonsten kannst Du probieren, einen primären Schlüssel auf die score-table zu setzen, bei der nach gameID+score+userid sortiert wird. Dann könnte der JOIN ausversehen das richtige Element rausgeben.

// EDIT: Ich hab es mal bei einer testdatenbank bei mir getestet ... allerdings unter 5.0 ....

SELECT
        gl.gameID, gl.gameName, gl.gamePath, gl.gamePicture,
        s.score AS maxScore, s.userID, u.name AS username
FROM
        backend_gamesarea_gamelist AS gl
LEFT JOIN
        backend_gamesarea_scorelist AS s
ON
        s.gameID = gl.gameID
LEFT JOIN
        backend_usertable AS u
ON
        u.id = s.userID
WHERE
        gl.categoryID = '1'
GROUP BY
        gl.gameID
ORDER BY
        gl.gameID ASC

und als score-table:

CREATE TABLE `backend_gamesarea_scorelist` (
  `userID` int(11) NOT NULL,
  `score` bigint(20) NOT NULL,
  `gameID` int(11) NOT NULL,
  PRIMARY KEY  (`gameID`,`score`,`userID`)
)

Bei mir funktioniert es.

gameID  gameName            gamePath                            gamePicture                         maxScore  userID  username  
1       Bomberman           ./games/abenteuer/bomberman.swf     ./games/abenteuer/bomberman.gif     NULL      NULL    NULL 
2       Streets of fire     ./games/abenteuer/streetsoffire.swf ./games/abenteuer/streetsoffire.gif 123       1       webmaster 
3       Indianajones        ./games/abenteuer/indianajones.swf  ./games/abenteuer/indianajones.gif  NULL      NULL    NULL 
4       Megaman             ./games/abenteuer/megaman.swf       ./games/abenteuer/megaman.gif       NULL      NULL    NULL 
5       Links Rupee Madness ./games/abenteuer/zeldaruppee.swf   ./games/abenteuer/zeldaruppee.gif   NULL      NULL    NULL

Gruß,
Jumper, the II.

[Lars]

Du kriegst zwar die richtige User-ID, aber den falschen Score-Datensatz.

[JumperII]

Stimmt ... bliebe noch die Lösung, ohne MAX( score) und Group By auszukommen und die Sortierung nach GameID und Score desc zu setzen .... dann ist es im Resultat immer der erste Eintrag zur GameID ...

Gruß,
Jumper, the II.