GROUP BY geeft niet het juiste resultaat

Door Bryan De Baar op 08-02-2020 22:43 gewijzigd op 08-02-2020 22:44

2.822 views


teams
+--+------+
|ID| TEAM |
|1 |Yellow|
|2 |Green |
|3 |Blue  |
|4 |Red   |
+--+------+

Players
+--+------+
|ID| NAME |
|1 |Ben   |
|2 |Mike  |
|3 |Jason |
+--+------+

Lineups
+---+---+
|PID|TID|
|1  |3  |
|2  |1  |
|2  |4  |
|3  |2  | 
+--+----+


<?php
$sql	= "	SELECT teams.id teamsid, teams.team teamname, lineups.tid, players.name 
			FROM teams 
			LEFT JOIN lineups ON lineups.tid = teams.id 
			AND lineups.pid = 2
			LEFT JOIN players ON lineups.pid = players.id
			GROUP BY players.name
			ORDER BY team
		  ";
$result = mysqli_query($connection, $sql);

while($row = mysqli_fetch_assoc($result)){
	echo $row['name'];
	$checked = ($row['tid'] === NULL ? '' : ' checked="checked"');
?>
<label>
	<input type="checkbox" <?php echo $checked; ?>><?php echo $row['teamname']; ?>
</label><?php
}
?>

De voorlaatste regel in de query GROUP BY players.name zorgt ervoor dat de data niet word weergegeven zoals ik zou willen.
Zonder deze regel zou het resultaat zijn


<label><input type="checkbox">Blauw</label>
<label><input type="checkbox">Geel</label>
"Ben"
<label><input type="checkbox" checked="checked">Groen</label>
"Ben"
<label><input type="checkbox" checked="checked">Rood</label>

Maar ik wil de player naam 1 keer laten voorkomen in het resultaat, ik dacht dan gebruik ik GROUP BY maar dit levert als resultaat op


<label><input type="checkbox">Geel</label>
"Ben"
<label><input type="checkbox" checked="checked">Rood</label>

Het resultaat dat ik graag zou willen is


"Ben"
<label><input type="checkbox">Blauw</label>
<label><input type="checkbox">Geel</label>
<label><input type="checkbox" checked="checked">Groen</label>
<label><input type="checkbox" checked="checked">Rood</label>

Wat moet ik aanpassen hiervoor?

Ger van Steenderen

14-02-2020 18:31

Bryan De Baar op 08/02/2020 22:43:47

De voorlaatste regel in de query GROUP BY players.name zorgt ervoor dat de data niet word weergegeven zoals ik zou willen.

Dat klopt, de SQL-standaard stelt dat alle kolommen die niet in een aggregate functie staan moeten worden vermeld in de GROUP BY, tenzij ze volledig afhankelijk zijn van een kolom die er wel in voorkomt. In MySQL heb je de FULL_GROUP_BY flag waarmee je dit aan of uit kan zetten.

Bryan De Baar op 08/02/2020 22:43:47

Maar ik wil de player naam 1 keer laten voorkomen in het resultaat, ik dacht dan gebruik ik GROUP BY maar dit levert als resultaat op
<label><input type="checkbox">Geel</label>
"Ben"
<label><input type="checkbox" checked="checked">Rood</label>
Het resultaat dat ik graag zou willen is
"Ben"
<label><input type="checkbox">Blauw</label>
<label><input type="checkbox">Geel</label>
<label><input type="checkbox" checked="checked">Groen</label>
<label><input type="checkbox" checked="checked">Rood</label>
Wat moet ik aanpassen hiervoor?

Dat de naam meerdere keren voorkomt in het resultaat is inherent aan MySQL, dit is zonder ingrepen niet te veranderen, het simpelste is om dit in php af te vangen:


<?php
$result = mysqli_query($connection, $sql);
$flag = 0;
while($row = mysqli_fetch_assoc($result)){
    	if (!$flag) {
		echo $row['name'];
		$flag = 1;
	}
    	$checked = ($row['tid'] === NULL ? '' : ' checked="checked"');
	
?>
<label>
    <input type="checkbox" <?php echo $checked; ?>><?php echo $row['teamname']; ?>
</label><?php
}
?>

Dan over de query zelf, in deze situatie lijkt het mij handiger een CROSS JOIN te gebruiken:


SELECT p.player_name, t.team_name, l.player_id
FROM players p
CROSS JOIN teams t
LEFT JOIN lineups l ON l.player_id = p.player_id AND l.team_id = t.team_id
WHERE p.player_id = 2

De CROSS JOIN geeft je alle mogelijke combinaties van teams / spelers, dit heeft als voordeel dat je dit kan gebruiken voor de benadering van zowel spelers en teams. Plus dat het ook werkt voor meerdere spelers en/of team (dit in tegenstelling met de oplossing via de LEFT JOIN)

Reageren

Inloggen om te reageren