php - MySQL删除记录前如何与另一张表的数据进行比较

Question

我有一个名为 Enrollment 的数据库，所有学生都将被添加到批处理和类(class)中。 stud_id、course_id 和batch_id 是指带有 fk 的其他表。

 enrolment
+----------+---------+-----------+----------+
| enrol_id | stud_id | course_id | batch_id |
+----------+---------+-----------+----------+
|        1 |       1 |         2 |        1 |
|        2 |       5 |         3 |        2 |
|        3 |       4 |         5 |        3 |
+----------+---------+-----------+----------+

在删除student表中的学生记录之前，我想比较students表中的stud_id和 enrollment 表中的 stud_id。如果匹配，则不要删除该学生。或者某种消息说这条记录与登记表相关联，或类似的东西。

如果匹配，我想运行这个 $sql = "DELETE FROM students WHERE student_id = $id ";

students
+---------+------------+-----------+
| stud_id | first_name | last_name |
+---------+------------+-----------+
|       1 | John       | Doe       |
|       2 | Susy       | Roberts   |
|       3 | John       | redneck   |
+---------+------------+-----------+

请帮忙!!

Answer 1

可以使用NOT IN

来实现

DELETE FROM students
WHERE student_id NOT IN (SELECT stud_id 
                    FROM enrolment) AND student_id = $id

可以使用NOT EXISTS

实现

DELETE FROM students
WHERE NOT EXISTS(SELECT NULL
                FROM enrolment
               WHERE stud_id = $id) AND student_id = $id