[MySQL] Probleem met lastige query

[Mini-me]

Ik ben bezig om een website te maken waarop de uitslagen van een spel kunnen worden bijgehouden. Omdat het aantal deelnemers per keer kan varieren, heb ik gekozen voor de volgende databasestructuur:

speler
spelerid	naam

uitslag
id	wedstrijdid	spelerid	score

wedstrijd
wedstrijdid	datum

De bedoeling is dat per wedstrijd de winnaar wordt getoond met de score die hij gehaald heeft. Hiervoor moet een join van de drie tabellen genomen worden en vervolgens wordt per wedstrijdid de maximale score genoemd, met de naam van de bijbehorende persoon.

Op dit moment heb ik de volgende query:

code:

SELECT uitslag.wedstrijdid AS wedstrijdid, 
wedstrijd.datum AS datum, 
speler.naam AS naam, 
uitslag.score AS score 
FROM wedstrijd 
INNER JOIN uitslag ON wedstrijd.wedstrijdid=uitslag.wedstrijdid 
INNER JOIN speler ON uitslag.spelerid=speler.spelerid

Deze query levert echter slechts de gecombineerde tabellen en niet de maximale score. Ik heb al zitten klooien met GROUP BY, MAX(score) etc maar nog geen resultaat . Voor mijn gevoel zit ik er vrij dicht bij in de buurt..
Iemand enig idee hoe de query afgemaakt moet worden?

[intGod]

Je hebt 2 queries nodig, 1 om per wedstrijd de maximale score te berekenen en een volgende query om de speler er terug aan te hangen. Helaas kent MySql geen subqueries en volgens mij kan je in mySql ook geen subquery in je FROM clause gebruiken dus daar ben je het haasje. Je moet dus vanuit PHP je recordset openen en vervolgens per record de speler ophalen.

code:

select
  uitslag.wedstrijdId
, max(score)
from
  wedstrijd
     inner join
  uitslag
    on wedstrijd.wedstrijdId on uitslag.wedstrijdId
    left join
  speler
    on uitslag.spelerId = speler.spelerId
group by
  uitslag.wedstrijdId

code:

select
  speler.naam
, speler.spelerId
, wedstrijd.datum
from
  wedstrijd
     inner join
  uitslag
    on wedstrijd.wedstrijdId on uitslag.wedstrijdId
    left join
  speler
    on uitslag.spelerId = speler.spelerId
where
  uitslag.score = $intScore
and wedstrijd.wedstrijdId = $intWedstrijdIf

[Verwijderd]

Misschien maar MySQL lapt de regels betreffende grouping en het gebruiken van group functies wel eens aan zijn laars dus probeer eens MAX() in de WHERE te gebruiken ookal mag dat technisch niet. Waarschijnlijk kan je ook wel de naam van de speler uitlezen ondanks dat je die niet mee in de GROUP BY genomen hebt.
Allemaal vage dingen dus waarmee je je toch naar het gewenste resultaat kan klooien. Alternatief is om een betere DBM te gebruiken.

[bigtree]

Het lijkt er inderdaad op dat MySQL hier raar omspringt met de GROUP BY in combinatie met MAX(). Als je echter het commentaar in de manual leest, zie je dat je dit ook op kan lossen zonder een GROUP BY, en wel zo:

code:

SELECT uitslag.wedstrijdid,
wedstrijd.datum,
speler.naam,
uitslag.score
FROM uitslag 
LEFT JOIN uitslag AS uitslag2 ON 
(uitslag2.wedstrijdid = uitslag.wedstrijdid AND uitslag.score < uitslag2.score) 
INNER JOIN speler ON uitslag.spelerid = speler.spelerid
INNER JOIN wedstrijd ON wedstrijd.wedstrijdid = uitslag.wedstrijdid
WHERE uitslag2.wedstrijdid IS NULL

[Rotjeknor]

intGod schreef op 08 May 2003 @ 22:50:
Je hebt 2 queries nodig, 1 om per wedstrijd de maximale score te berekenen en een volgende query om de speler er terug aan te hangen. Helaas kent MySql geen subqueries en volgens mij kan je in mySql ook geen subquery in je FROM clause gebruiken dus daar ben je het haasje. Je moet dus vanuit PHP je recordset openen en vervolgens per record de speler ophalen.

offtopic:
MySQL kent vanaf versie 4.1 dus wel subselects.

[Mini-me]

bigtree schreef op 09 May 2003 @ 00:59:
Het lijkt er inderdaad op dat MySQL hier raar omspringt met de GROUP BY in combinatie met MAX(). Als je echter het commentaar in de manual leest, zie je dat je dit ook op kan lossen zonder een GROUP BY, en wel zo:

code:

--de goede query--

Relaxed, dit levert inderdaad het juiste resultaat. Bedankt!

[ Voor 29% gewijzigd door Mini-me op 09-05-2003 14:15 ]

[Mini-me]

Wat ik me nou afvraag: deze query levert dus alle hoogste scores op, die via een count(uitslag.score) te tellen zijn. Op die manier krijg je per persoon het aantal winstpartijen ook te zien.
Is het ook mogelijk om het totale aantal wedstrijden in diezelfde query op te vragen? Dat scheelt nogal wat werk, omdat je anders met php steeds een nieuwe query moet maken waarbij de wedstrijden worden geteld (wat opzich een eenvoudige query is, alleen in combinatie met de eerder genoemde oplossing vrij lastig).

[bigtree]

Dat gaat niet lukken. Aangezien het maar één getal is kun je hier sowieso beter een andere query voor maken.

[Mini-me]

Sorry, ik heb het niet helemaal duidelijk gevraagd. Het gaat om het aantal wedstrijden per persoon, dus worden dat in ieder geval meer dan 1 queries.

[bigtree]

Het aantal wedstrijden per persoon kan met één query.
"SELECT COUNT(*)" en "GROUP BY" zijn je vrienden.
huiswerk

[intGod]

Rotjeknor schreef op 09 May 2003 @ 08:35:
[...]

offtopic:
MySQL kent vanaf versie 4.1 dus wel subselects.

Vraagje: kan mySql dit:

code:

select
  myId
from
  (
    select
       userId
    from
      tbHuppeldepup
  )
    as tbPommetjeHorlepiep

of

code:

select
  myId
, (select top 1 anderVeld from tbTafeltjeDekjeEzeltjeStrekje where TafeltjeDekjeEzeltjeStrekjeId = tbVanMij.TafeltjeDekjeEzeltjeStrekjeId_Fk) as appelId
from
 tbVanMij