Een tabel Goals met daarin alle goals die gevallen zijn zoals onderstaand:
id-wedstrijd_id-leden_id-minuut
1-2-29-3
2-2-55-5
3-2-55-10
Nu wil ik alle goals van 1 persoon bij elkaar optellen en dan wil ik die goals ORDER BY maken.
Via de leden_id haal ik op in welk team het lid speelt, zodat ik daar later ook nog wat mee kan.
Maar als ik onderstaande query gebruik geeft hij aan dat bijvoorbeeld lid 29 4x gescoord heeft, dit komt -volgens mij- omdat het leden_id meerdere keren in Teamindeling voor komt, omdat deze persoon namelijk in meerdere teams speelt/coacht.
Ik heb het volgende geprobeerd:
<?
SELECT
l.voornaam,
l.achternaam,
COUNT(g.leden_id),
FROM
Goals as g
LEFT JOIN
Leden AS l
ON
(g.leden_id = l.id)
LEFT JOIN
Teamindeling AS t
ON
(g.leden_id = t.leden_id) AND t.seizoen = '2013/2014'
WHERE
g.leden_id != '0'
GROUP BY
g.leden_id
?>
Met hindsight was ie inderdaad incompleet. Ik had toen de gedachte: een speler speelt bij één team en/of is coach bij één of meer andere teams.
Alleen jij stelde dat het niet uit maakt waar de subquery staat, en dat is onjuist. Als je de volgorde in het voorbeeld omdraait krijg je een ander resultaat.
Dat ligt eraan wat je wil hebben inderdaad. Als je ook de spelers wilt hebben in het overzicht die 0 keer hebben gescoord dan krijg je die niet als je eerst de goals telt.
Ok, nu ben ik hier wat verder op in aan het gaan, en wil ik mijn scores apart per team. Ik krijg dit goed voor elkaar, alleen zit ik nog met de mensen die niet gescoord hebben, deze laat hij nu dus niet zien.
Dit heb ik nu: (ik dacht met een COALESCE te kunnen werken, maar dit lijkt niet goed)
<?
SELECT
lid.voornaam,
lid.achternaam,
lid.team,
lid.id,
COALESCE(COUNT(g.leden_id), 0) AS aantal_goals
FROM
(SELECT
l.id,
l.voornaam,
l.achternaam,
t.team
FROM
Leden AS l
JOIN
Teamindeling AS ti
ON
(l.id = ti.leden_id)
JOIN
Teams AS t
ON
(t.id = ti.team_id)
WHERE
ti.taak = 'Speler'
AND
ti.seizoen = '2013/2014'
AND
t.team = 'ZSC 5'
GROUP BY
l.id,
l.voornaam,
l.achternaam
) AS lid
LEFT JOIN
Goals AS g
ON
(lid.id = g.leden_id) AND g.seizoen = '2013/2014' AND g.wie LIKE 'ZSC%'
LEFT JOIN
Wedstrijden AS w
ON
(g.wedstrijd_id = w.id)
WHERE
w.soort = 'Competitie'
GROUP BY
lid.voornaam,
lid.achternaam,
lid.team
ORDER BY
aantal_goals DESC
Coalesce op count heeft geen zin, count geeft altijd wat terug (in tegenstelling tot de andere aggegrate functies).
Maar wat je niet goed doet is een WHERE op een kolom uit een tabel via een left join. Daarmee vervalt het effect van de left join want je filtert de rijen waarvan w.soort NULL zijn eruit.
Oplossing is een geneste join of een subquery.
Geneste join:
.....
LEFT JOIN
(goals g
INNER JOIN
wedstrijden w
ON (g.wedstrijd_id = w.id) AND (w.soort = 'Competitie')
)
ON
(lid.id = g.leden_id) AND g.seizoen = '2013/2014' AND g.wie LIKE 'ZSC%'
Subquery:
LEFT JOIN
(SELECT
leden_id
FROM goals
JOIN wedstrijden w
ON goals.wedstrijd_id = w.id
WHERE
g.seizoen = '2013/2014' AND g.wie LIKE 'ZSC%'
AND w.soort = 'Competitie') g
Ook is de eerste subquery niet meer nodig, die was alleen nodig om te voorkomen dat leden die in meerdere teams spelen dubeel geselecteerd worden, maar nu je het per team doet is dat niet meer nodig.
Als ik die subquery in die left join gebruik, moet ik dan ook nog een ON hebben?
Ik heb nu dit: (krijg 1 speler te zien :))
<?
$query = "
SELECT
l.voornaam,
l.achternaam,
COUNT(go.leden_id) aantal_goals
FROM
Leden AS l
LEFT JOIN
(SELECT
leden_id
FROM
Goals AS g
JOIN
Wedstrijden AS w
ON
g.wedstrijd_id = w.id
WHERE
g.seizoen = '". $seizoen. "' AND g.wie LIKE 'ZSC%'
AND
w.soort = 'Competitie')
AS go
ON
(l.id = go.leden_id)
JOIN
Teamindeling AS t
ON
(l.id = t.leden_id)
JOIN
Teams AS team
ON
(team.id = t.team_id)
WHERE
t.taak = 'Speler'
AND
t.seizoen = '". $seizoen. "'
AND
t.team_id = '". $teamid. "'
ORDER BY
aantal_goals DESC
";
?>
