JOIN met 3 tabellen
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
a.aantal,
a.actie,
g.voornaam,
g.achternaam,
SUM(IF(g.id = r.gastid, 1,0)) as test
FROM
gasten g
JOIN
acties a
ON
g.id = a.gastid
INNER JOIN
cal_reservatie r
ON
g.id = r.gastid
WHERE
a.actie <> 'webform' AND a.aantal <> ''
a.aantal,
a.actie,
g.voornaam,
g.achternaam,
SUM(IF(g.id = r.gastid, 1,0)) as test
FROM
gasten g
JOIN
acties a
ON
g.id = a.gastid
INNER JOIN
cal_reservatie r
ON
g.id = r.gastid
WHERE
a.actie <> 'webform' AND a.aantal <> ''
Als ik de laatste JOIN weglaat heb ik alle records die ik nodig heb maar zoals het er staat krijg ik maar 1 record te zien.
Er zijn 3 tabellen: gasten, reservaties en acties. De bedoeling is om alle gasten te hebben in een tabel die ook aan een bepaalde actie hebben meegedaan in combinatie met het aantal reservaties die die gasten ook (eventueel) hebben gedaan. Hier kan het aantal uiteraard 0 zijn want niet iedereen heeft al een reservatie gemaakt.
Kan mij hier iemand mee helpen?
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.
Gewijzigd op 16/09/2016 11:41:44 door Willem vp
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.
Gewijzigd op 16/09/2016 12:20:15 door Brecht S
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
Code (php)
1
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:
Code (php)
1
2
3
4
2
3
4
voornaam | achternaam | actie
=======================================================
Piet | Janssen | Twee kroketten voor n euro
Piet | Janssen | Tweede frikandel gratis
=======================================================
Piet | Janssen | Twee kroketten voor n euro
Piet | Janssen | Tweede frikandel gratis
Wat wil je met SUM nu precies (op)tellen ?
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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
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.
Of: Welk resultaat zou je willen krijgen?
Toevoeging op 16/09/2016 22:42:57:
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.
Toevoeging op 16/09/2016 23:01:07:
Ik breid mijn eerdere voorbeeldje iets uit:
Code (php)
1
2
3
4
5
2
3
4
5
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
=================================================================
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:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?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>';
}
?>
// ...
$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:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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.
(
[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.
Gewijzigd op 16/09/2016 23:16:00 door Frank Nietbelangrijk
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.
Heb je hiermee genoeg info?
Je hebt een tabel acties en die noem je nu events..
Je hebt restaurants (Je zou dit een locatie kunnen noemen) maar heb je hier dan ook een tabel voor?
Je hebt gasten. Dit mag ik zien als leden of gebruikers toch?
Je hebt reservaties: Hierin staat dan (als het goed is) wie wanneer en waar (gast_id, datumtijd en locatie)
Kortom ik vraag me af of je database indeling wel klopt. Het vervreemd me ook dat je een kolom aantal in acties hebt.
Heb je misschien een KLEINE mysql dump met een paar voorbeeld records?
De tabel gasten zijn de restaurantbezoekers. Reservaties worden door hen gemaakt. De gast_id hangt aan de tabel gasten id. Datum en tijd wanneer ze komen eten inderdaad. Locatie heeft hier niks mee te maken. Het is maar 1 restaurant.
Bvb dat bepaalde restaurant doet af en toe een wijndegustatie, een bezoek aan een boerderij waar het vlees vandaan komt, enz... Hiervoor moeten mensen inschrijven en die gegevens worden dan in de tabel acties gezet. Daar is dus ook een koppeling met de gast_id (= veld id in de tabel gasten).
In acties heb ik inderdaad een kolom aantal omdat ze moeten inschrijven dus het aantal is het aantal personen die ze meebrengen bij hun inschrijving. Er kunnen dus ook mensen inschrijven die nog geen reservatie hebben gemaakt in het restaurant dus daarom zei ik dat de kolom van de SUM ook 0 kan zijn omdat iedereen kan inschrijven.
Kan je volgen of moet je nog een mysql dump hebben?
Gewijzigd op 17/09/2016 21:51:11 door Brecht S
Dan wil je eigenlijk een overzicht creëren van wie er naar welke actie is geweest MET als extra informatie hoe vaak deze persoon een tafeltje heeft gereserveerd. Dit laatste staat dan totaal los van het overzicht eigenlijk omdat we hiervoor de tabel reservaties moeten hebben in plaats van acties.
Volgens mij is een sub-query dan de oplossing (als ik jou nu goed begrepen heb tenminste)
Ik heb er dit van kunnen maken, je zult het nog wat moeten tweaken lijkt mij
Code (php)
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
SELECT a.actie, g.voornaam, g.achternaam, r.reserveringen
FROM gasten g
LEFT JOIN acties a
ON g.id = a.gast_id
LEFT JOIN
(
SELECT g.id as gasten_id, COUNT(g.id) as reserveringen FROM gasten g
JOIN reservatie r ON r.gast_id = g.id
GROUP BY g.id
) r ON r.gasten_id = g.id
ORDER BY a.actie, g.voornaam
FROM gasten g
LEFT JOIN acties a
ON g.id = a.gast_id
LEFT JOIN
(
SELECT g.id as gasten_id, COUNT(g.id) as reserveringen FROM gasten g
JOIN reservatie r ON r.gast_id = g.id
GROUP BY g.id
) r ON r.gasten_id = g.id
ORDER BY a.actie, g.voornaam
De query tussen de haakjes wordt als eerste uitgevoerd. Hiermee wordt een gast_id teruggegeven en daarbij het aantal reserveringen op naam van deze gast. Dit resultaat is vervolgens de tweede JOIN van je (buitenste) query en voegt de kolom met de extra info toe.
Ik heb de query aangepast zoals ik denk dat het zou moeten zijn:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT
a.aantal,
a.actie,
g.voornaam,
g.achternaam,
g.id as gid,
r.reserveringen
FROM
gasten g
LEFT JOIN
acties a
ON
g.id = a.gastid
LEFT JOIN
(
SELECT g.id as gasten_id, COUNT(g.id) as reserveringen FROM gasten g
JOIN cal_reservatie r ON r.gastid = g.id
GROUP BY g.id
)
ON
r.gastid = g.id
WHERE
a.actie <> 'webform' AND a.aantal <> ''
ORDER BY
a.actie, g.voornaam
a.aantal,
a.actie,
g.voornaam,
g.achternaam,
g.id as gid,
r.reserveringen
FROM
gasten g
LEFT JOIN
acties a
ON
g.id = a.gastid
LEFT JOIN
(
SELECT g.id as gasten_id, COUNT(g.id) as reserveringen FROM gasten g
JOIN cal_reservatie r ON r.gastid = g.id
GROUP BY g.id
)
ON
r.gastid = g.id
WHERE
a.actie <> 'webform' AND a.aantal <> ''
ORDER BY
a.actie, g.voornaam
Maar nu krijg ik een error: Every derived table must have its own alias
Gewijzigd op 18/09/2016 13:40:26 door Brecht S
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SELECT
a.aantal,
a.actie,
g.voornaam,
g.achternaam,
g.id as gid,
r.reserveringen
FROM
gasten g
LEFT JOIN
acties a
ON
g.id = a.gastid
LEFT JOIN
(
SELECT
ga.id as gasten_id,
COUNT(r.couverts) as reserveringen
FROM
gasten ga
JOIN
cal_reservatie r
ON
r.gastid = ga.id
GROUP BY
ga.id
)
ON
r.gastid = ga.id
WHERE
a.actie <> 'webform' AND a.aantal <> ''
ORDER BY
a.actie, g.voornaam
a.aantal,
a.actie,
g.voornaam,
g.achternaam,
g.id as gid,
r.reserveringen
FROM
gasten g
LEFT JOIN
acties a
ON
g.id = a.gastid
LEFT JOIN
(
SELECT
ga.id as gasten_id,
COUNT(r.couverts) as reserveringen
FROM
gasten ga
JOIN
cal_reservatie r
ON
r.gastid = ga.id
GROUP BY
ga.id
)
ON
r.gastid = ga.id
WHERE
a.actie <> 'webform' AND a.aantal <> ''
ORDER BY
a.actie, g.voornaam
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SELECT
a.aantal,
a.actie,
g.voornaam,
g.achternaam,
g.id as gid,
sq.reserveringen
FROM
gasten g
LEFT JOIN
acties a
ON
g.id = a.gastid
LEFT JOIN
(
SELECT
ga.id as gasten_id,
COUNT(r.couverts) as reserveringen
FROM
gasten ga
JOIN
cal_reservatie r
ON
r.gastid = ga.id
GROUP BY
ga.id
) sq
ON
r.gastid = ga.id
WHERE
a.actie <> 'webform' AND a.aantal <> ''
ORDER BY
a.actie, g.voornaam
a.aantal,
a.actie,
g.voornaam,
g.achternaam,
g.id as gid,
sq.reserveringen
FROM
gasten g
LEFT JOIN
acties a
ON
g.id = a.gastid
LEFT JOIN
(
SELECT
ga.id as gasten_id,
COUNT(r.couverts) as reserveringen
FROM
gasten ga
JOIN
cal_reservatie r
ON
r.gastid = ga.id
GROUP BY
ga.id
) sq
ON
r.gastid = ga.id
WHERE
a.actie <> 'webform' AND a.aantal <> ''
ORDER BY
a.actie, g.voornaam
Maar nu een andere error: Unknown column 'r.gastid' in 'on clause'
Alhoewel er wel degelijk een r.gastid bestaat...
ON sq.gasten_id = g.id
bedoelt.
Toevoeging op 18/09/2016 15:12:28:
En in je eerdere post zei je toch dat 'aantal' al was aangepast naar een integer? Zie ik in je query niet terug. ;-)
Ja, ik had integer al juist gezet, maar heb het terug moeten zetten omdat er in de achterliggende applicatie updates kunnen gedaan worden aan die kolom en dan gaat het fout.
Ik moet daar ook nog een paar aanpassingen voor doen om alles goed te krijgen, maar wil eerst mijn query hier goed krijgen...
Ik heb de fout gevonden en de query nogmaals aangepast en ook een extra veld toegevoegd, namelijk van diegenen die al eerder hebben gereserveerd met hoeveel man ze in totaal geweest zijn dan. Vb iemand heeft al eerder 3 reservaties gemaakt en is op die 3 reservaties in totaal komen eten met bvb 15 man.
Ik post nog even de nieuwe en enige werkende query om anderen te helpen:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
SELECT
a.aantal,
a.actie,
g.voornaam,
g.achternaam,
g.id as gid,
r.reserveringen,
r.couverts
FROM
gasten g
LEFT JOIN
acties a
ON
g.id = a.gastid
LEFT JOIN
(
SELECT
ga.id as gasten_id,
COUNT(ga.id) as reserveringen,
SUM(IF(r.couverts != '', r.couverts,0)) as couverts,
r.gastid
FROM
gasten ga
JOIN
cal_reservatie r
ON
r.gastid = ga.id
GROUP BY
ga.id
) r
ON
r.gastid = g.id
WHERE
a.actie <> 'webform' AND a.aantal <> ''
ORDER BY
a.actie, g.voornaam
a.aantal,
a.actie,
g.voornaam,
g.achternaam,
g.id as gid,
r.reserveringen,
r.couverts
FROM
gasten g
LEFT JOIN
acties a
ON
g.id = a.gastid
LEFT JOIN
(
SELECT
ga.id as gasten_id,
COUNT(ga.id) as reserveringen,
SUM(IF(r.couverts != '', r.couverts,0)) as couverts,
r.gastid
FROM
gasten ga
JOIN
cal_reservatie r
ON
r.gastid = ga.id
GROUP BY
ga.id
) r
ON
r.gastid = g.id
WHERE
a.actie <> 'webform' AND a.aantal <> ''
ORDER BY
a.actie, g.voornaam
Gewijzigd op 18/09/2016 15:58:01 door Brecht S