Lastige (sub)query
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?
vb: Hoe is lesuur opgebouwd.
eventueel een volledige structuur van beide tabellen.
Code (php)
1
show create table tablename
Jan
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
CREATE TABLE `lessen` (
`id_lessen` int(4) NOT NULL AUTO_INCREMENT,
`groep` varchar(6) DEFAULT NULL,
`lesuur` varchar(3) DEFAULT NULL,
`vak` varchar(3) DEFAULT NULL,
`docent` varchar(3) DEFAULT NULL,
`lokaal` varchar(3) DEFAULT NULL,
PRIMARY KEY (`id_lessen`)
) ENGINE=MyISAM AUTO_INCREMENT=1581 DEFAULT CHARSET=latin
`id_lessen` int(4) NOT NULL AUTO_INCREMENT,
`groep` varchar(6) DEFAULT NULL,
`lesuur` varchar(3) DEFAULT NULL,
`vak` varchar(3) DEFAULT NULL,
`docent` varchar(3) DEFAULT NULL,
`lokaal` varchar(3) DEFAULT NULL,
PRIMARY KEY (`id_lessen`)
) ENGINE=MyISAM AUTO_INCREMENT=1581 DEFAULT CHARSET=latin
Code (php)
1
2
3
4
5
2
3
4
5
CREATE TABLE `lesuurnr` (
`lesuurnr` int(3) NOT NULL,
`lesuur` varchar(3) NOT NULL,
PRIMARY KEY (`lesuurnr`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
`lesuurnr` int(3) NOT NULL,
`lesuur` varchar(3) NOT NULL,
PRIMARY KEY (`lesuurnr`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
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".
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.
Gewijzigd op 09/10/2020 10:44:33 door Ivo P
Dan zou de volgende query voldoende moeten zijn:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT
`lesuurnr`.`lesuurnr`,
`lesuurnr`.`lesuur`
FROM `lesuurnr`
WHERE `lesuurnr`.`lesuur` NOT IN (
SELECT `lessen`.`lesuur`
FROM `lessen`);
`lesuurnr`.`lesuurnr`,
`lesuurnr`.`lesuur`
FROM `lesuurnr`
WHERE `lesuurnr`.`lesuur` NOT IN (
SELECT `lessen`.`lesuur`
FROM `lessen`);
Hiervoor heb je dan een lokalen-tabel nodig, maar die is sowieso zinnig om te hebben zoals @Ivo en ik al aangaven.
Structuur
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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);
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
Code (php)
1
2
3
2
3
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');
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
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
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
);
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
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
+-----------------+-----------------+
| tsl_description | loc_description |
+-----------------+-----------------+
| ma1 | lokaal B |
| ma1 | lokaal C |
| ma2 | lokaal C |
| ma3 | lokaal A |
| ma3 | lokaal B |
+-----------------+-----------------+
| 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
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
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;
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
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
+----------+---------+
| locatie | vrij |
+----------+---------+
| lokaal A | ma3 |
| lokaal B | ma1,ma3 |
| lokaal C | ma1,ma2 |
+----------+---------+
| locatie | vrij |
+----------+---------+
| lokaal A | ma3 |
| lokaal B | ma1,ma3 |
| lokaal C | ma1,ma2 |
+----------+---------+
Zonder kennis van SQL zou hij het nog sneller kunnen doen in Excel..
Kleine opmerking over VARCHAR.
In MySQL kan je tot VARCHAR(65535) gaan, de limiet van 255 is historisch en dient verder geen praktisch nut. Zo zullen er geen klaslokalen zijn met namen van 4 volle regels. Omdat er geen index op de namen hoeft zou je evenwel TEXT als datatype kunnen gebruiken, zonder vooraf opgegeven limiet.
Voor zover ik weet kan VARCHAR in combinatie met MyISAM (voor MySQL versie 8) en met tijdelijke in-memory tabellen met de MEMORY engine sneller werken dan TEXT, omdat data dan in-row wordt opgeslagen wat I/O zou schelen. Maar ik heb dat nooit uitgezocht.
Toen ik eenmaal om was naar Postgres heb ik over dat soort technisch geneuzel niet meer na hoeven denken, daar is TEXT en VARCHAR exact hetzelfde op technisch niveau. Dus ik heb alles maar meteen TEXT gemaakt, en hoefde ik ook geen foutmeldingen in PHP te bedenken voor als er een keer een waarde van <input type=text> binnenkomt die langer is dan 255 tekens.
Over het "valt buiten de opdracht": ik weet niet in welke hoedanigheid @Gijs aan deze tabellen/database werkt, maar een eerste stap in zo'n (bestaand?) systeem zou toch op zijn minst een stukje verkenning moeten zijn. Als je dan constateert dat er gewoon data ontbreekt voor het op een fatsoenlijke manier opvragen van de juiste informatie dan zou je dat simpelweg moeten terugkoppelen.
Tenzij hier andere ideeën over zijn, of dat het simpelweg niet kan? "Gaat tegen de opdracht in" is niet hetzelfde als "kan niet aangepast worden". Een locatie-tabel zou anders nogal handig zijn.
De database is op dit moment (gezien de nieuwe informatievraag) niet (langer) zo ver uitgenormaliseerd dat je hier lekker mee kunt werken. Een database zou zo ontworpen moeten zijn dat je hier ook weer makkelijk informatie uit kunt halen, als dit niet aan de orde is dan schort er iets aan het ontwerp, of het voldoet niet langer aan de nieuwe informatievraag.
Sidenote: docenten en vakken lijken ook niet te verwijzen naar aparte tabellen, zou misschien ook handig kunnen zijn om op den duur roosters uit te kunnen draaien voor zowel leerlingen alsook docenten?
Je zou zelfs kunnen stellen dat enige bestaande halsstarrigheid om dingen te veranderen -want zo klinkt het in mijn oren- nu misschien de voornaamste reden is dat de schoen knelt op het moment dat je meer/andere informatie uit zo'n systeem wilt opvragen... Als er verder geen concrete belemmering is, pas het gewoon aan en bouw de database verder uit?
Misschien is het ook handig dat de topicstarter aan kan geven waarom het aanpassen een probleem is?
En om antwoord te geven op de oorspronkelijke vraag (alle lege lokalen op een bepaald lesuur) zou je in mijn opzet de laatste query kunnen aanpassen tot
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
SELECT t.tsl_description AS lesuur, GROUP_CONCAT(l.loc_description) AS beschikbaar
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 t.tsl_id
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 t.tsl_id
Dit levert
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
+-----------+-------------------+
| lesuur | beschikbaar |
+-----------+-------------------+
| ma1 | lokaal B,lokaal C |
| ma2 | lokaal C |
| ma3 | lokaal A,lokaal B |
+-----------+-------------------+
| lesuur | beschikbaar |
+-----------+-------------------+
| ma1 | lokaal B,lokaal C |
| ma2 | lokaal C |
| ma3 | lokaal A,lokaal B |
+-----------+-------------------+
Gewijzigd op 10/10/2020 16:41:23 door Thomas van den Heuvel
Verder zou ik denken dat we genoeg aandacht hebben besteed aan een huiswerkopdracht.
Als iemand het tot werk zou willen verheffen heeft die meer aan een goed boek. Ik zou "Databases modelleren, bouwen en gebruiken" van Hofstede aan willen bevelen, maar die wordt niet meer (nieuw) verkocht.