MySQL course scheduler

So I have the following table containing the courses available for me to take:

"CREATE TABLE courses 
(
subject varchar(15), 
course int,
day varchar(15),
ti开发者_Python百科mein double,
timeout double,
courseID int
)";

For example: subject= ECON, course= 101, day= M, timein= 9.0, timeout= 10.3, courseID= 11. I need to find a MySQL script that will give me all possible combinations of the courses without having a time conflict. Thanks

---

This query searches all the courses who start in the given courses expect the courses it self.

SELECT *
FROM `courses`
WHERE `day` LIKE 'M'
AND `timein` >= 9.0 AND `timein` < 10.3
AND `courseID` !=11

---

So to extend my suggestion in the comments, I would perhaps normalise your table thus:

CREATE TABLE course
{
   ID int
   subject varchar(100)
}

CREATE TABLE lesson  
 ( 
    timeslotID int
    courseID int 
 ); 

CREATE TABLE timeslots
(
   ID int,
   day varchar(10),
   timein double,
   timeout double
)

You could also leave the day in the lesson table and have fewer timeslots, that makes most difference to how you query the data. Either way, your clash query would be a simple matter of finding all the matching timeslots. It is likely to result in more JOINs in your queries but much less duplication of data.

Obviously the restriction here is that your timeslots have to be discrete so if you had some lessons starting at 10:00 and some at 10:30 you would need to have 30 minute timeslots rather than an hour, for example, but if you're looking at a school or college I think this would probably be fine.

---

All possible combinations is an n-way cross product (join).

SQL can't figure out how many distinct time slots there are AND construct a select statement with that many joins. So you will have to punt and do that yourself.

Hence, for each unique time slot, join on all courses where the course does not overlap.

The resulting set of rows will N x M where N is the number of unique time slots and M is the number of possible combinations.

---

This might be a simplified version of what your are after - it doesn't include days, but that should be fairly simple to add in:

An example table with some data:

CREATE TABLE `courses` (
  `course_id` int(11) NOT NULL AUTO_INCREMENT,
  `time_in` time DEFAULT NULL,
  `time_out` time DEFAULT NULL,
  PRIMARY KEY (`course_id`)
) ENGINE=InnoDB;

INSERT INTO courses VALUES
(1, '09:00', '10:00'),
(2, '09:30', '10:30'),
(3, '10:00', '11:00'),
(4, '10:30', '11:30'),
(5, '12:00', '13:00');

For every row (each timeslot), determine if there are any other rows that do not overlap. Note that this allows for end and start times that are the same:

SELECT
  c1.course_id,
  c1.time_in,
  c1.time_out,
  c2.course_id,
  c2.time_in,
  c2.time_out
FROM courses AS c1
JOIN (
  SELECT course_id, time_in, time_out FROM courses
) AS c2
ON (c1.time_out > c2.time_in) XOR (c1.time_in < c2.time_out)
ORDER BY c1.course_id, c1.time_in;

+-----------+----------+----------+-----------+----------+----------+
| course_id | time_in  | time_out | course_id | time_in  | time_out |
+-----------+----------+----------+-----------+----------+----------+
|         1 | 09:00:00 | 10:00:00 |         3 | 10:00:00 | 11:00:00 |
|         1 | 09:00:00 | 10:00:00 |         4 | 10:30:00 | 11:30:00 |
|         1 | 09:00:00 | 10:00:00 |         5 | 12:00:00 | 13:00:00 |
|         2 | 09:30:00 | 10:30:00 |         4 | 10:30:00 | 11:30:00 |
|         2 | 09:30:00 | 10:30:00 |         5 | 12:00:00 | 13:00:00 |
|         3 | 10:00:00 | 11:00:00 |         1 | 09:00:00 | 10:00:00 |
|         3 | 10:00:00 | 11:00:00 |         5 | 12:00:00 | 13:00:00 |
|         4 | 10:30:00 | 11:30:00 |         5 | 12:00:00 | 13:00:00 |
|         4 | 10:30:00 | 11:30:00 |         2 | 09:30:00 | 10:30:00 |
|         4 | 10:30:00 | 11:30:00 |         1 | 09:00:00 | 10:00:00 |
|         5 | 12:00:00 | 13:00:00 |         1 | 09:00:00 | 10:00:00 |
|         5 | 12:00:00 | 13:00:00 |         2 | 09:30:00 | 10:30:00 |
|         5 | 12:00:00 | 13:00:00 |         3 | 10:00:00 | 11:00:00 |
|         5 | 12:00:00 | 13:00:00 |         4 | 10:30:00 | 11:30:00 |
+-----------+----------+----------+-----------+----------+----------+