Read 1116 times |
Created 2016-05-18 03:40:08 |
Updated 2016-05-18 04:21:05 |
|
|
CREATE TABLE praktikum(
id INT AUTO_INCREMENT PRIMARY KEY,
waktu_awal_prak TIME,
waktu_akhir_prak TIME
);
INSERT INTO praktikum(waktu_awal_prak,waktu_akhir_prak)
VALUES
('07:00:00','08:30:00'),
('08:40:00','10:10:00'),
('10:20:00','11:50:00'),
('12:00:00','13:30:00'),
('13:40:00','15:10:00'),
('15:20:00','16:50:00');
SELECT * FROM praktikum;
+----+-----------------+------------------+
| id | waktu_awal_prak | waktu_akhir_prak |
+----+-----------------+------------------+
| 1 | 07:00:00 | 08:30:00 |
| 2 | 08:40:00 | 10:10:00 |
| 3 | 10:20:00 | 11:50:00 |
| 4 | 12:00:00 | 13:30:00 |
| 5 | 13:40:00 | 15:10:00 |
| 6 | 15:20:00 | 16:50:00 |
+----+-----------------+------------------+
6 rows in set (0.00 sec)
CREATE TABLE jadwal_mk(
id INT AUTO_INCREMENT PRIMARY KEY,
waktu_awal_mk TIME,
waktu_akhir_mk TIME
);
INSERT INTO jadwal_mk(waktu_awal_mk,waktu_akhir_mk)
VALUES
('07:50:00','08:40:00'),
('07:50:00','08:40:00'),
('08:40:00','09:30:00'),
('08:40:00','09:30:00'),
('12:00:00','12:50:00'),
('12:50:00','13:40:00'),
('13:40:00','14:30:00');
SELECT * FROM jadwal_mk;
+----+---------------+----------------+
| id | waktu_awal_mk | waktu_akhir_mk |
+----+---------------+----------------+
| 1 | 07:50:00 | 08:40:00 |
| 2 | 07:50:00 | 08:40:00 |
| 3 | 08:40:00 | 09:30:00 |
| 4 | 08:40:00 | 09:30:00 |
| 5 | 12:00:00 | 12:50:00 |
| 6 | 12:50:00 | 13:40:00 |
| 7 | 13:40:00 | 14:30:00 |
+----+---------------+----------------+
7 rows in set (0.00 sec)
SELECT
a.*,COUNT(*) as jml
FROM
praktikum a
JOIN jadwal_mk b ON
(a.waktu_awal_prak>=b.waktu_akhir_mk OR a.waktu_akhir_prak<=b.waktu_awal_mk)
GROUP BY a.id
HAVING jml=(SELECT COUNT(*) FROM jadwal_mk);
+------+-----------------+------------------+-----+
| id | waktu_awal_prak | waktu_akhir_prak | jml |
+------+-----------------+------------------+-----+
| 3 | 10:20:00 | 11:50:00 | 7 |
| 6 | 15:20:00 | 16:50:00 | 7 |
+------+-----------------+------------------+-----+
2 rows in set (0.00 sec)