Read 292 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)