在右表上使用条件SELECT进行条件LEFT INNER JOIN查询

Before I type all this, I have tried and tried to find the solution to this from other threads here and by searching google. But I just cant seem to get the answer. I consider myself pretty good with SQL but I just cant seem to get to grips with this problem at all.

在我输入所有这些之前,我已尝试并尝试从此处的其他线程和搜索谷歌找到解决方案。但我似乎无法得到答案。我认为自己对SQL非常好,但我似乎无法解决这个问题。

I currently have two tables:

我目前有两张桌子:

I am trying to get an array of ref_departments.id and ref_departments.department_name, which will be used as a dropdown on the frontend to populate the company_department_norm table. It should best be pointed out at this point that I have no technical issues in creating the dropdown etc - it is being built in the Yii framework and the actual dropdown itself is already in place. My problem is identifying the correct SQL statement to bring back on the data I want for populating the dropdown (I will be using Yii's Chtml helper class).

我正在尝试获取一个ref_departments.id和ref_departments.department_name数组,它将用作前端的下拉列表以填充company_department_norm表。在这一点上最好指出我在创建下拉列表时没有技术问题 - 它正在Yii框架中构建,实际的下拉列表已经到位。我的问题是确定正确的SQL语句来恢复我想要填充下拉列表的数据(我将使用Yii的Chtml助手类)。

The first condition of the SELECT statement I am looking to create is one where the query on the ref_departments table will only select rows where the values in the id (PK) column are not found in the company_department_norm.department_id column. This done so only valid options are available in the list. I have this query working, and it looks like this...

我想要创建的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)

Additional integrity/data validation for this will also be done on the back end (in the Yii model class).

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

The next step of this is the part I am totally stuck on. Within the Yii model for company_department_norm, the company_id field states the FK for the company (as you can see above). This will be passed as a parameter into the query to add an additional condition to the query so that the JOIN statement will only match rows on the right table (company_department_name) where company_id is the same as the company_id passed from the model. For testing purposes, I am simply trying to get this working with a company_id of integer 1 hard-coded into the SQL statement. However, for the life of me, I just cant seem to work out how to do this.

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

I have attempted to modify with above query using this: -

我试图用上面的查询修改:

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

However, this just doesn't seem to filter the right table at all.

但是,这似乎根本没有过滤掉正确的表格。

Can anyone help me with this? I would imagine that more experienced SQL users than I would be able to identify the solution quite quickly but I have been pulling my hair out for hours trying to solve this!

谁能帮我这个?我认为比我更有经验的SQL用户能够很快识别出解决方案但是我已经把我的头发拉了几个小时试图解决这个问题!

2 个解决方案

#1

You can put the company_id condition in the ON clause:

您可以将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)

This returns any department that isn't represented yet at company 1, versus your first query returns any department that isn't represented yet at any company.

这将返回在公司1尚未表示的任何部门,而您的第一个查询将返回任何公司尚未表示的任何部门。

---

Re your comment: Given your example data, this query won't show any difference between using the company_id condition or not, because both departments are represented at company 1.

重新评论:根据您的示例数据,此查询不会显示使用company_id条件之间的任何差异,因为两个部门都在公司1中表示。

I have tested this on MySQL 5.5 and it works fine. Here's another example with more data that demonstrates better:

我在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);

Now query for depts not in company 1, and it correctly shows "IT":

现在查询不在公司1中的depts,它正确显示“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              |
+----+-----------------+

Now query for depts not in company 2. Even though company 2 names Accounts and HR, it doesn't have the numeric department_id on which the join condition is based. So it thinks those two depts are not matched.

现在查询不在公司2中的depts。即使公司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              |
+----+-----------------+

If you're getting some other result, you either have not used the query as I described, or your data is not as you described.

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

#2

SELECT RD.id, RD.department_name 
FROM ref_departments RD
WHERE 
    NOT EXISTS (
    SELECT 1 FROM company_department_norm 
    WHERE 
        company_id = 1 
    AND department_id = RD.id
)

PS: you should consider dropping the department_name column of your company_department_norm table: 6NF

PS:您应该考虑删除company_department_norm表的department_name列:6NF

SQLFIDDLE: http://sqlfiddle.com/#!2/553a4/2