mysql - 如何在mysql中根据sql条件排除结果？

Question

我正在使用 Codeigniter 和 MySQL 创建工作申请门户。我有以下表格:

• jobs - 包含作业列表
• students - 包含学生列表
• jobs_applied - 包含学生申请的工作列表

工作

主键:job_id。

列:job_id、job_name

学生

主键:student_id。

列:student_id、student_name、student_email

jobs_applied

主键:jobs_applied_id。

外键:job_id, student_id。

列:job_id、student_id、status、applied_date

在我的应用程序中，当用户申请工作时，我将 jobs_applied 中的状态列更改为“pending”。我想从列表中删除已申请的职位。

到目前为止，我正在使用以下查询来列出作业。

SELECT * FROM jobs

我尝试过对 jobs_applied 使用 INNER JOIN，但我被困在其中。我不知道如何将其从列表中排除。我的 session 中有 student_id。

如何使用 student_id 作为条件创建排除学生申请的工作的查询？

Answer 1

您想要针对特定​​学生对 jobs_applied 执行 LEFT JOIN，如果没有匹配项，jobs_applied.* 将为 NULL，这就是您的 WHERE 条件。

这是一个演示:

mysql> CREATE TABLE `jobs` (
    ->   `job_id` int NOT NULL,
    ->   PRIMARY KEY (`job_id`)
    -> );

mysql> INSERT INTO `jobs` VALUES (11),(22);

mysql> CREATE TABLE `students` (
    ->   `student_id` int NOT NULL,
    ->   PRIMARY KEY (`student_id`)
    -> );

mysql> INSERT INTO `students` VALUES (100),(200);

mysql> CREATE TABLE `jobs_applied` (
    ->   `jobs_applied_id` int NOT NULL,
    ->   `job_id` int DEFAULT NULL,
    ->   `student_id` int DEFAULT NULL,
    ->   `status` int DEFAULT NULL,
    ->   PRIMARY KEY (`jobs_applied_id`),
    ->   KEY `job_id` (`job_id`),
    ->   KEY `student_id` (`student_id`),
    ->   CONSTRAINT `jobs_applied_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`),
    ->   CONSTRAINT `jobs_applied_ibfk_2` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`)
    -> );

mysql> INSERT INTO `jobs_applied` VALUES (1,11,100,NULL),(2,22,200,NULL);

学生 100 申请了职位 11，因此他在后续查询中应该只能看到职位 22。

mysql> SELECT j.*
    -> FROM jobs j LEFT JOIN jobs_applied ap
    ->  ON j.job_id = ap.job_id AND ap.student_id = 100
    -> WHERE ap.job_id IS NULL;
+--------+
| job_id |
+--------+
|     22 |
+--------+

同样，学生 200 申请了工作 22，因此她之后应该只能看到工作 11。

mysql> SELECT j.*
    -> FROM jobs j LEFT JOIN jobs_applied ap
    ->  ON j.job_id = ap.job_id AND ap.student_id = 200
    -> WHERE ap.job_id IS NULL;
+--------+
| job_id |
+--------+
|     11 |
+--------+