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 <> ''
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?
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?
Het gaat hier over maar 1 restaurant die af en toe acties doet. De woordkeuze is eigenlijk verkeerd want dat gaat hier over events. Maar om de naam aan te passen is het nu te laat. Er zit een volledige reservatiepakket achter die goed loopt al 2 jaar.
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?
Goed. Er is maar 1 restaurant. Dat begrijp ik. En normale reserveringen van klanten die komen eten komen in de tabel reservaties. Daarnaast zijn er speciale arrangementen en de tabel hiervoor heet acties.
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
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
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:
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
Maar nu krijg ik een error: Every derived table must have its own alias
Ik heb de query nog eens aangepast maar nog steeds dezelfde foutmelding:
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
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
Maar nu een andere error: Unknown column 'r.gastid' in 'on clause'
Alhoewel er wel degelijk een r.gastid bestaat...
Nee voorstel werkt niet. Dan heb ik een unknown kolom.
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:
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
