php - mysql multi-table query

Question

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

Answer 1

This is easy to solve:

1. Find out all the courses this student has signed up for

2. 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.

Answer 2

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:

select * from r left join d on r.cid = d.cid where d.day = 2 and (d.start between 13 and 15 or d.end between 13 and 15)

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

Answer 3

This kind of problem can be solved by self-connection.

select tmp1.sid,tmp1.cid,tmp2.cid 
    from 
        (select a.sid,a.cid,b.day,b.start,b.end from tbl1 a inner join tbl2 on a.cid=b.cid) tmp1 
        left join  (select a.sid,a.cid,b.day,b.start,b.end from tbl1 a inner join tbl2 on a.cid=b.cid) tmp2 
            on tmp1.sid = tmp2.sid and tmp1.cid<>tmp2.cid and tmp1.day = tmp2.day
    where (tmp1.start > tmp2.start and tmp1.start < tmp2.end) 
        or (tmp1.end > tmp2.end and tmp1.end < tmp2.end)