Hallo allemaal,
Ik ben bezig met een school rooster database, bestaande uit meerdere tabellen, maar de focus ligt bij mijn vraag op twee tabellen hiervan. Ik wil graag dat de uitkomst van mijn query alle lege lokalen weergeeft op een bepaald lesuur.
In de eerste tabel, tabel1, staan de lesuren, maandag het eerste als ma1, woensdag 7e als wo7, enzovoort. Ook staat er een key in voor elk lesuur.
In de tweede tabel, tabel2, staan de lessen. Hierin staat lokaal, docent, vak, lesuur, groep, en les id.
Zelf kwam ik niet verder dan een lijst krijgen van alle lokalen (select lokaal from tabel2 group by lokaal order by lokaal). Hoe pas ik deze query aan (subquery??) zodat de output alle lege lokalen op een bepaald lesuur zijn?
Ik zou ook een lokalen-tabel verwachten? Indien een lokaal altijd beschikbaar is dan kun je dit nooit afleiden uit bovenstaande tabellen, daar het alleen bezette tijden/locaties betreft en dat lokaal zal daar dan dus nooit in voorkomen.
In een "lege lokalen query" zouden de (alle aanwezige) lokalen een goed uitgangspunt vormen denk ik. Je hebt die informatie namelijk nodig om de "ruimte van beschikbare lokalen op te spannen".
Nope die is er geen, de andere twee tabellen zijn gegevens over de klas en over leerlingen. Het is niet de eerste keer dat deze opdracht gegeven is, dus het zou te doen moeten zijn met een (sub)query. Misschien trouwens met een beetje php? Ik heb namelijk echt geen idee ik loop helemaal vast
Lijkt mij handig, je kunt dan namelijk een soort van (denkbeeldige) matrix maken met op de ene as de lokalen en op de andere as de lesuurnummers die je vervolgens loslaat op de lessen. De "gaten" in deze matrix vormen dan de lokalen die op een bepaald tijdstip beschikbaar zijn.
Je zou uit de tabel Lessen weliswaar alle lokalen kunnen opzoeken, maar stel in lokaal 99 is nooit les.
Dan staat dat lokaal niet in Lessen, en zou je die dus ook niet als "vrij" kunnen vinden.
Dat betekent ook dat je aan het begin van het jaar (nog geen lessen in je rooster) sowieso geen enkel vrij lokaal gaat vinden.
Ik zou in Lessen trouwens ook geen kolom Lesuur varchar(3) verwachten, maar een Lesuurnr int()
net als voor lokaal, docent etc.
?
Onbekende gebruiker
09-10-2020 16:17
gewijzigd op 09-10-2020 16:18
Dus `lessen` koppelt lesinformatie met de les-uren in de tabel `lesuurnr` via de gedeelde kolommen `lessen`.`lesuur` en `lesuurnr`.`lesuur`. En je wilt een lijstje hebben van alle rijen in `lesuurnr` die nog niet gekoppeld zijn, ofwel die nog vrij zijn.
Dan zou de volgende query voldoende moeten zijn:
SELECT
`lesuurnr`.`lesuurnr`,
`lesuurnr`.`lesuur`
FROM `lesuurnr`
WHERE `lesuurnr`.`lesuur` NOT IN (
SELECT `lessen`.`lesuur`
FROM `lessen`);
Of je maakt van de lesuren en lokalen een cartesisch product (CROSS JOIN) en vergelijkt dit dan met lesuren.
Hiervoor heb je dan een lokalen-tabel nodig, maar die is sowieso zinnig om te hebben zoals @Ivo en ik al aangaven.
Structuur
CREATE TABLE locations (
loc_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
loc_description VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE timeslots (
tsl_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
tsl_description VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE lessons (
les_tsl_id INT(10) UNSIGNED NOT NULL,
les_loc_id INT(10) UNSIGNED NOT NULL,
les_description VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE lessons ADD CONSTRAINT id_lessons PRIMARY KEY (les_tsl_id, les_loc_id);
ALTER TABLE lessons ADD FOREIGN KEY (les_tsl_id) REFERENCES timeslots(tsl_id);
ALTER TABLE lessons ADD FOREIGN KEY (les_loc_id) REFERENCES locations(loc_id);
Data
INSERT INTO locations (loc_description) VALUES ('lokaal A'), ('lokaal B'), ('lokaal C');
INSERT INTO timeslots (tsl_description) VALUES ('ma1'), ('ma2'), ('ma3');
INSERT INTO lessons (les_tsl_id, les_loc_id, les_description) VALUES (1,1,'Frans'), (2,1,'Nederlands'), (2,2,'Duits'), (3,3,'Engels');
Query
SELECT t.tsl_description, l.loc_description
FROM locations l
CROSS JOIN timeslots t
WHERE NOT EXISTS (
SELECT 1
FROM lessons
WHERE les_tsl_id = t.tsl_id
AND les_loc_id = l.loc_id
);
Levert
+-----------------+-----------------+
| tsl_description | loc_description |
+-----------------+-----------------+
| ma1 | lokaal B |
| ma1 | lokaal C |
| ma2 | lokaal C |
| ma3 | lokaal A |
| ma3 | lokaal B |
+-----------------+-----------------+
Of als je dit voor de leesbaarheid wilt groeperen op lokaal
SELECT l.loc_description AS locatie, GROUP_CONCAT(t.tsl_description) AS vrij
FROM locations l
CROSS JOIN timeslots t
WHERE NOT EXISTS (
SELECT 1
FROM lessons
WHERE les_tsl_id = t.tsl_id
AND les_loc_id = l.loc_id
)
GROUP BY l.loc_id;
Levert
+----------+---------+
| locatie | vrij |
+----------+---------+
| lokaal A | ma3 |
| lokaal B | ma1,ma3 |
| lokaal C | ma1,ma2 |
+----------+---------+
