mysql - 从 3 个表中选择行

Question

我很欣赏有很多教程和示例解释 JOIN 概念 - 但是我正在努力将这些示例应用到我的特定场景中。我希望得到一些帮助，如果要求不是太多的话，请详细说明解决方案中发生的情况以实现所需的结果。

3 个表:用户、评估、评估日志。

用户

+--------+------+------+-----------+----------+-----------+-------+--------+-----------+---------------+------+----------+
| UserID | User | Pass | FirstName | LastName | LastLogin | Email | Mobile | Kenitalla | AccountStatus | Role | Operator |
+--------+------+------+-----------+----------+-----------+-------+--------+-----------+---------------+------+----------+

评估

+--------------+------+-----------+-----------+-----------+-----------+-----------+--------------+----------+---------+----------+
| AssessmentID | Name | Criteria1 | Criteria2 | Criteria3 | Criteria4 | Criteria5 | RoleRequired | Required | Renewal | Operator |
+--------------+------+-----------+-----------+-----------+-----------+-----------+--------------+----------+---------+----------+

评估日志

+-----------------+------+----------------+------------+--------+-----------+----------+----------+----------+----------+----------+----------+---------+---------------+---------+
| AssessmentLogID | Date | AssessmentName | AssessedBy | UserID | StaffName | Comments | Verdict1 | Verdict2 | Verdict3 | Verdict4 | Verdict5 | Verdict | RenewalPeriod | NextDue |
+-----------------+------+----------------+------------+--------+-----------+----------+----------+----------+----------+----------+----------+---------+---------------+---------+

当用户进行评估时，评估日志中会记录一个条目。有些评估是必需的(“必需”列中的正确或错误)，而有些评估是可选的。 RoleRequired 列规定了需要评估的用户角色。

我想生成一份用户列表以及他们尚未通过的评估。评估必须是必需的，并且所需的角色必须与用户角色相匹配。如果评估日志中缺少“Verdict”列中的“Pass”条目，则表示评估尚未通过。

简单来说，我正在寻找一个将实现以下结果的查询:

+------------------+-----------------+----------------+
| assessments.Name | users.FirstName | users.LastName |
+------------------+-----------------+----------------+

在需要评估的情况下(Required 等于 true)，RequiredRole 与 users.Role 列匹配，并且评估日志中没有 Verdict 列包含“通过”值的评估条目。

如果需要进一步说明，请告诉我。

提前致谢!

Answer 1

我建议使用 LEFT JOIN。

首先，我们生成评估和用户的叉积，其中包含每个用户的角色所需的所有评估。

然后，LEFT JOIN 检查这些评估是否已通过。

SELECT ...
FROM
    users u
    JOIN assessments a ON (a.RoleRequired = u.Role)
    LEFT JOIN assessment_logs al ON (
            al.AssessmentID = a.AssessmentID
        AND al.UserID = u.UserID
        AND al.Verdict='Pass'
    )

WHERE 
        a.required
    AND al.UserID IS NULL