MySQL 关系选择问题

Question

我正在尝试选择已添加用户的所有工作，但排除他们拒绝的工作。我一直在调查 NOT EXISTS，但我无法用它排除被拒绝的工作。

表:拒绝

+---------+---------+---------+----------+
|      id |  job_id | user_id | declined |
+---------+---------+---------+----------+
|      15 |     223 |      25 |        1 |
|     100 |     156 |      50 |        1 |
|     125 |     651 |      60 |        1 |
+---------+---------+---------+----------+

当前查询。选择他们已添加到的所有工作，以及他们已拒绝的工作。

SELECT
    job.*, applicants.*, declined.*
FROM job
    JOIN applicants ON job.job_id = applicants.job_id
    LEFT OUTER JOIN declined ON job.job_id = declined.job_id
WHERE applicants.user_id = '" .  $userId . "' AND applicants.recruited = 1

失败的尝试。这显然没有选择任何记录。

SELECT
    job.*, applicants.*, declined.*
FROM job
    JOIN applicants ON job.job_id = applicants.job_id
    LEFT OUTER JOIN declined ON job.job_id = declined.job_id
WHERE applicants.user_id = '" .  $userId . "' AND applicants.recruited = 1 AND declined.declined = 0

Answer 1

您可以使用 NOT IN 来排除被拒绝的工作:

SELECT
    job.*, applicants.*
FROM job
    JOIN applicants ON job.job_id = applicants.job_id
WHERE applicants.user_id = '" .  $userId . "' AND applicants.recruited = 1
AND job.job_id not in (
  SELECT DISTINCT job_id from declined where user_id = '" .  $userId . "' and declined = 1
)

还有一件事 - 不确定您使用的是什么语言，但您不应该那样设置 $userId，使用参数化查询，它比那样构建查询更安全。