mysql - 在右表上使用条件 SELECT 的条件 LEFT INNER JOIN 查询

Question

在我输入所有这些之前，我已经尝试并尝试从此处的其他线程和搜索谷歌找到解决方案。但我似乎无法得到答案。我认为自己的 SQL 相当不错，但我似乎根本无法解决这个问题。

我目前有两个表:

我正在尝试获取 ref_departments.id 和 ref_departments.department_name 的数组，它们将用作前端的下拉列表以填充 company_department_norm 表。在这一点上最好指出的是，我在创建下拉列表等方面没有技术问题 - 它是在 Yii 框架中构建的，实际的下拉列表本身已经到位。我的问题是确定正确的 SQL 语句以返回我想要填充下拉列表的数据(我将使用 Yii 的 Chtml 帮助程序类)。

我要创建的 SELECT 语句的第一个条件是对 ref_departments 表的查询将仅选择 中的值的行在 company_department_norm.department_id 列中找不到 id (PK) 列。这样做是为了在列表中仅提供有效选项。我有这个查询工作，它看起来像这样......

SELECT RD.id, RD.department_name FROM ref_departments RD 
LEFT OUTER JOIN company_department_norm CDN
ON (CDN.department_id = RD.id) 
WHERE (CDN.department_id IS NULL)

额外的完整性/数据验证也将在后端完成(在 Yii 模型类中)。

下一步是我完全坚持的部分。在 company_department_norm 的 Yii 模型中，company_id 字段表示公司的 FK(如上所示)。这将作为参数传递到查询中以向查询添加附加条件，以便 JOIN 语句将仅匹配右表 (company_department_name) 中的行，其中 company_id 与从模型传递的 company_id 相同。出于测试目的，我只是尝试使用整数 1 的 company_id 硬编码到 SQL 语句中。然而，对于我的生活，我似乎无法弄清楚如何做到这一点。

我尝试使用以下方法修改上述查询:-

LEFT OUTER JOIN (SELECT * FROM company_department_norm WHERE 
company_department_norm.company_id = 1) CDN

但是，这似乎根本无法过滤出正确的表格。

谁能帮我解决这个问题？我可以想象，比我更有经验的 SQL 用户能够很快确定解决方案，但我已经绞尽脑汁好几个小时试图解决这个问题!

Answer 1

您可以将 company_id 条件放在 ON 子句中:

SELECT RD.id, RD.department_name FROM ref_departments RD 
LEFT OUTER JOIN company_department_norm CDN
ON (CDN.department_id = RD.id AND CDN.company_id = 1) 
WHERE (CDN.department_id IS NULL)

这会返回尚未代表公司 1 的任何部门，而您的第一个查询返回尚未代表任何公司的任何部门。

---

关于您的评论:根据您的示例数据，此查询不会显示使用 company_id 条件与否之间的任何区别，因为两个部门都代表公司 1。

我已经在 MySQL 5.5 上测试过了，它运行良好。这是另一个示例，其中包含更多数据，可以更好地展示:

use test;

drop table if exists ref_departments;
create table ref_departments (
 id int primary key,
 department_name varchar(20)
);

insert into ref_departments values
(1, 'Accounts'),
(2, 'HR'),
(3, 'IT');

drop table if exists company_department_norm;
create table company_department_norm (
 id int primary key,
 company_id int,
 department_name varchar(20),
 department_id int
);

insert into company_department_norm values
(1, 1, 'Accounting', 1),
(2, 1, 'HR', 2),
(3, 2, 'Accounts', NULL),
(4, 2, 'HR', NULL),
(5, 2, 'IT', 3);

现在查询不在公司 1 的部门，它正确显示“IT”:

SELECT RD.id, RD.department_name FROM ref_departments RD 
LEFT OUTER JOIN company_department_norm CDN
ON (CDN.department_id = RD.id AND CDN.company_id = 1) 
WHERE (CDN.department_id IS NULL);

+----+-----------------+
| id | department_name |
+----+-----------------+
|  3 | IT              |
+----+-----------------+

现在查询不在公司 2 中的部门。即使公司 2 名称 Accounts 和 HR，它也没有加入条件所基于的数字 department_id。所以它认为这两个部门不匹配。

SELECT RD.id, RD.department_name FROM ref_departments RD 
LEFT OUTER JOIN company_department_norm CDN
ON (CDN.department_id = RD.id AND CDN.company_id = 2) 
WHERE (CDN.department_id IS NULL);

+----+-----------------+
| id | department_name |
+----+-----------------+
|  1 | Accounts        |
|  2 | HR              |
+----+-----------------+

如果您得到一些其他结果，您要么没有按照我描述的那样使用查询，要么您的数据与您描述的不一样。