JOIN met 3 tabellen

Om te beginnen schrik ik van de conditie a.aantal <> ''. Dat suggereert dat je de aantallen opslaat als string en niet als integer. Not good. ;-)

Verder zeg je dat het kan voorkomen dat niet iedereen reservaties heeft gemaakt. In dat geval zou ik een left join gebruiken in plaats van een inner join (overigens is er in MySQL geen onderscheid tussen een 'gewone' join en een inner join, maar dat terzijde). Die (inner) join zorgt ervoor dat je alleen gasten krijgt die wél een reservatie gemaakt hebben.

De aantallen werden inderdaad opgeslaan als string maar is ondertussen al aangepast naar een integer ;-)
Ik heb de inner join al vervangen naar een LEFT JOIN maar ik krijg het niet goed. Ik heb meerdere keren dezelfde namen die verschijnen en dat zit zo niet in de database.
Als ik de regel met de SUM laat staan krijg ik maar 1 resultaat door en dat is dus ook niet juist maar als ik die ene regel weglaat krijg ik dus dubbele records die eigenlijk niet bestaan in db.

SUM is een aggregate functie en dus mis ik ook een GROUP BY.

Als we de SUM even buiten beschouwing laten dan klopt het dat je meerdere rijen krijgt met dezelfde namen.

Even een simpel voorbeeldje: Piet Janssen heeft aan twee acties meegedaan. Met de query

SELECT a.actie, g.voornaam, g.achternaam FROM gasten g JOIN acties a ON g.id = a.gastid

Je zult dan twee records krijgen als resultaat, iets in de trant van:

voornaam | achternaam | actie
=======================================================
Piet     | Janssen    | Twee kroketten voor één euro
Piet     | Janssen    | Tweede frikandel gratis

Wat wil je met SUM nu precies (op)tellen ?

De aangepaste nieuwe query die nu wel werkt maar er ontbreken resultaten:

		    SELECT
			  a.aantal,
			  a.actie,
			  g.voornaam,
			  g.achternaam,
			  g.id as gid,
			  SUM(IF(g.id = r.gastid, 1,0)) as test
			FROM
			  gasten g
			JOIN
			  acties a
			ON
			  g.id = a.gastid
			LEFT JOIN
			  cal_reservatie r
			ON
			  g.id = r.gastid
			WHERE
			  a.actie <> 'webform' AND a.aantal <> ''
			GROUP BY
			  g.id
			ORDER BY
			  a.actie

Ik denk dat er iets is met de GROUP BY... Ik krijg dus iemand die op 2 acties is ingeschreven (events) maar 1 keer te zien. De SUM telt gewoon het aantal keren dat iemand heeft gereserveerd om te komen eten in het restaurant maar die blijkt wel de juiste cijfers te bevatten.

Nogmaals mijn vraag: Wat wil je met SUM nu precies (op)tellen ?

Of: Welk resultaat zou je willen krijgen?

De GROUP BY is hoe dan ook niet geldig. De enige geldige GROUP met jouw query is met a.aantal, a.actie, g.voornaam, g.achternaam en g.id. Dus alle velden die geen aggregate zijn.

maar: WAT wil je optellen?



[size=xsmall]Toevoeging op 16/09/2016 22:42:57:[/size]

Als je alle records wilt hebben dan heb je de aggregate functie niet nodig. In dat geval kun je binnen PHP ook in een loopje tellen.

[size=xsmall]Toevoeging op 16/09/2016 23:01:07:[/size]

Ik breid mijn eerdere voorbeeldje iets uit:

id | voornaam | achternaam | actieId | actie
=================================================================
1  | Piet     | Janssen    | 1       | Twee kroketten voor n euro
1  | Piet     | Janssen    | 2       | Tweede frikandel gratis
2  | Jan      | Nelissen   | 2       | Tweede frikandel gratis

Dan kun je in PHP zelfs prachtige array's maken:
<?php
// ...

$array = array();

while($row = mysqli_fetch_assoc($result))
{
$array[$row['id']]['voornaam'] = $row['voornaam'];
$array[$row['id']]['achternaam'] = $row['achternaam'];
$array[$row['id']]['acties'][$row['actieId']]['id'] = $row['actieId'];
$array[$row['id']]['acties'][$row['actieId']]['actie'] = $row['actie'];
}

echo '<pre>' . print_r($array, TRUE) . '</pre>';

foreach($array as $persoon)
{
echo $persoon['voornaam'] . ' heeft aan ' . count($persoon['acties']) .
' verschillende acties meegedaan.<br>';
}
?>

resultaat:

Array
(
    [1] => Array
        (
            [voornaam] => Piet
            [achternaam] => Janssen
            [acties] => Array
                (
                    [1] => Array
                        (
                            [id] => 1
                            [actie] => Twee kroketten voor n euro
                        )

                    [2] => Array
                        (
                            [id] => 2
                            [actie] => Tweede frikandel gratis
                        )

                )

        )

    [2] => Array
        (
            [voornaam] => Jan
            [achternaam] => Nelissen
            [acties] => Array
                (
                    [2] => Array
                        (
                            [id] => 2
                            [actie] => Tweede frikandel gratis
                        )

                )

        )

)

Piet heeft aan 2 verschillende acties meegedaan.
Jan heeft aan 1 verschillende acties meegedaan.

Ben van Velzen op 16/09/2016 22:37:47

De GROUP BY is hoe dan ook niet geldig. De enige geldige GROUP met jouw query is met a.aantal, a.actie, g.voornaam, g.achternaam en g.id. Dus alle velden die geen aggregate zijn.

Je loopt een jaar of 20 achter. ;-)

De SQL99-standaard en later staan het toe dat je nonaggregate kolommen niet in de GROUP BY opneemt als die functioneel afhankelijk zijn van de kolommen in de GROUP BY clause. Als g.id een primary key is (wat denk ik redelijk veilig is om aan te nemen) dan wordt in dit geval voldaan aan die functionele afhankelijkheid en hoeven de overige kolommen dus niet genoemd te worden.

De meeste databases lopen achter, en dit deel uit de standaard is optioneel, dus nee ik loop niet echt achter. MySQL weigert ze ook nog steeds wanneer je strict mode aanzet. PostgreSQL weigert ze ook. Andere databases heb ik geen ervaring mee.

@Frank: de SUM zou het aantal keren moeten weergeven dat iemand is komen eten in het restaurant. Dus die kan ook 0 zijn, want het hoofddoel is dat we willen weten wie naar welke actie is geweest (een actie is eigenlijk een soort event).
Heb je hiermee genoeg info?