[MySQL] Records selecteren op basis van beschikbaarheid

[Verwijderd]

Hallo Tweakers, eerste post hier.. Zit met de handen in het haar over hoe ik het volgende op kan lossen in MySQL. Hopelijk kan iemand me een zetje in de goede richting geven.

Ik ben een online inschrijfsysteem aan het maken, dat bestaat uit een vijftal tabellen te weten:

Invites
invite_id (PK/AI)
location_id (FK)
group_id (FK)
[..]

groups
group_id (PK/AI)
group_name

locations
location_id (PK/AI)
location_name

tours
tour_id (PK/AI)
location_id (FK)
tour_name

attendance
tour_id (FK)
invite_id (FK)

Het systeem gaat over het volgende. Mijn opdrachtgever organiseert een evenement op 2 locaties (tabel locations) waarvoor hij gasten uit wil nodigen (tabel invites) en online hun aanwezigheid op te nemen (tabel attendance).

De gasten geven hun aanwezigheid op door zich in te schrijven voor een tour. Een tour is een rondleiding waarvan er meerdere op een dag plaatsvinden. Als een invite aanwezig is, heeft deze zich dus ingeschreven voor een tour en komt deze terug in de koppeltabel attendance. Tot zo ver alles prima.

Waar het lastig begint te worden is dat deze tours gecapt worden met een hoeveelheid X bezoekers. Dus indien tour A een bezoekersaantal heeft van 40, moet deze uit niet meer uit de database worden gevist als zijnde beschikbaar. Bezoekers moeten hier namelijk niet meer op in kunnen schrijven; hij zit vol.

Wat ik dus zal moeten doen is data uit de tabel tours selecteren op basis van locatie_id en het aantal inschrijvingen (dmv count()?) in de tabel attendance met het corresponderende tour_id. Indien deze de cap overschrijden dan wil ik deze tour niet meer in m'n resultaat hebben.

De tabel groups is overigens voor verschillende permissies. Er zijn namelijk bezoekers en leveranciers die beiden van het inschrijfsysteem gebruik zullen gaan maken.

Alle hulp is welkom, alvast bedankt

Edit: wat ik zelf al heb geprobeerd..

SELECT tours.tour_id, tours.name, count(attendance.tour_id) AS count_tours
FROM tours, attendance
WHERE (tours.tour_id = attendance.tour_id)
HAVING count_tours < 20

Dit geeft me een resultaat van alle tours waar <20 inschrijvingen voor bestaan.
Wat ik natuurlijk wil is alleen de tours waar nog ruimte beschikbaar is, en dus ook die waar geen inschrijvingen voor zijn.

[ Voor 9% gewijzigd door Verwijderd op 25-03-2011 10:00 ]

[RobIII]

Verwijderd schreef op vrijdag 25 maart 2011 @ 09:31:
Hallo Tweakers, eerste post hier..

Hoi en welkom!

Verwijderd schreef op vrijdag 25 maart 2011 @ 09:31:
Alle hulp is welkom, alvast bedankt

Ik mis eigenlijk één belangrijk aspect in je post: wat heb je zelf al geprobeerd? Heb je al iets van een query? Hier op GoT verwachten we een aantal dingen van een topicstart; die staan beschreven in onze Quickstart. Eigen inzet is daarbij belangrijk;en dat is nou net het stukje dat ik mis in je topic. Zou je je topicstart (of middels een nieuwe post) willen aanvullen met deze informatie?

[ThinkPad]

SELECT COUNT binnen een IF statement plaatsen?

[ Voor 20% gewijzigd door ThinkPad op 25-03-2011 09:43 ]

[Verwijderd]

RobIII schreef op vrijdag 25 maart 2011 @ 09:36:
[...]

Hoi en welkom!


[...]

Ik mis eigenlijk één belangrijk aspect in je post: wat heb je zelf al geprobeerd? Heb je al iets van een query? Hier op GoT verwachten we een aantal dingen van een topicstart; die staan beschreven in onze Quickstart. Eigen inzet is daarbij belangrijk;en dat is nou net het stukje dat ik mis in je topic. Zou je je topicstart (of middels een nieuwe post) willen aanvullen met deze informatie?

Post aangepast, zie SQL voorbeeld onderaan

[Verwijderd]

Union erbij, en een veldje hoeveel er max met een tour meekunnen en deze gebruiken in je query ?

[ Voor 76% gewijzigd door Verwijderd op 25-03-2011 10:02 ]

[Verwijderd]

Verwijderd schreef op vrijdag 25 maart 2011 @ 10:02:
Union erbij, en een veldje hoeveel er max met een tour meekunnen en deze gebruiken in je query ?

Moet ik geen join gebruiken? Probleem is dat ik nu niet de tours krijg waar nog niemand zich voor ingeschreven heeft, terwijl die cap wel wordt gerespecteerd.

[Verwijderd]

Ik denk dat ik er ben:

SELECT tours.tour_id, tours.name, count(attendance.tour_id) as total
FROM tours
LEFT JOIN attendance ON tours.tour_id = attendance.tour_id
GROUP BY tours.name

Ik selecteer het tour_id, de name en het aantal inschrijvingen uit de tabel attendance.
Vervolgens join ik de data uit attendance met een left join, omdat het kan dat beide tabellen niet naar elkaar refereren.
Door GROUP BY te gebruiken worden ook de lege tours getoond.

Resultaat:

code:

tour_id     name    total
1   Tour A      1
2   Tour B      1
3   Tour C      0
4   Tour D      0

[RobIII]

Ter info: Hoe werken joins?
Maar wat je zeker even moet lezen: Hoe werkt dat GROUP BY nu eigenlijk?. Je moet namelijk alle velden die niet in aggregate functies worden gebruikt opnemen in de Group By

[Maranello]

LEFT JOIN op de Tours

SQL:

SELECT tours.tour_id, tours.tour_name, count(attendance.tour_id) AS count_tours
FROM tours LEFT JOIN attendance ON tours.tour_id = attendance.tour_id
GROUP BY tours.tour_id, tours.tour_name
HAVING NOT count_tours > 20

Om het af te maken een HAVING om de "volle" tours eruit te halen

@Reinier: Dat ontbreekt in het inderdaad nog aan in de database. Extra kolom tour_max aanmaken, lijkt me?

[ Voor 16% gewijzigd door Maranello op 25-03-2011 11:28 ]

[Reinier]

^^^ Dat gaat nog niet goed als niet alle tours hetzelfde maximum hebben. Dat zal er nog bij moeten lijkt me.

[Verwijderd]

Maranello schreef op vrijdag 25 maart 2011 @ 11:16:
LEFT JOIN op de Tours

SQL:

SELECT tours.tour_id, tours.tour_name, count(attendance.tour_id) AS count_tours
FROM tours LEFT JOIN attendance ON tours.tour_id = attendance.tour_id
GROUP BY tours.tour_id, tours.tour_name
HAVING NOT count_tours > 20

Om het af te maken een HAVING om de "volle" tours eruit te halen

@Reinier: Dat ontbreekt in het inderdaad nog aan in de database. Extra kolom tour_max aanmaken, lijkt me?

Dat is inderdaad de uiteindelijk query, bedankt

Iedere tour heeft hetzelfde maximum aantal dat ik d.m.v. een PHP constant set. Het is echter wel netter om een tour_max aan te maken ja