There are two tables, briefly describe them.
Table 1:
Relationship table between courses and students r
sid cid
1 ｜ 1
1 ｜ 2
Student number 1 has chosen courses with cid 1 and 2
Table 2:
Specific schedule of courses d
cid day start end
1 ｜ 2 ｜ 14 ｜ 16
2 ｜ 2 ｜ 13 ｜ 15
Course cid is 1 on Tuesday 14:00-16:00
In addition, there is the student table s, sid sname. Course schedule c, cid cname
Here it can be seen from r and d that 1 this student chose two conflicting courses. How to use sql statement to query such conflicting records?
That is, what about the record of a student sid choosing a cid with a time conflict?
You can use the connection to query the cid corresponding to the sid and the corresponding time. Then how to determine whether the courses conflict in time?
Attachment: The scenario of this question is what I saw in a question set. I also agree to make judgments when selecting courses
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
This is easy to solve:
Find out all the courses this student has signed up for
Check if there is any time conflict between these courses
Check to see if they are on the same day. If they are on the same day, compare the start and end times to see if there is any conflict or overlap
No problem if they are not on the same day
Added:
However, this issue should be avoided when registering, and no conflicts are allowed.
When registering, check whether there is a time conflict between the course time he has registered for and the courses he has already registered for. If there is a conflict, registration will not be allowed.
As for comparing two time periods on the timeline to see if there is any conflict, it is actually easy to calculate
A - B, C - D
A - C - B or A - D - B
C - A - D or C - B - D
There will be an intersection between the four situations, indicating a time conflict.
Actually, I think that such conflicting courses should be judged when students choose courses, and they should not be added to the database
Method:
Every time when a student selects a course, first use the time of the selected course to query whether the current conflicting data already exists in the database
For example:
Suppose a student with sid = 1 has chosen a course with cid = 1, and then when choosing a course with cid = 2, the sql will be as follows:
What is checked in this way is whether there is a selected course within that time period on that day. If there is a result, it means that the time of the course you selected conflicts with the course you signed up for
This kind of problem can be solved by self-connection.