MySQL 左连接条件

Left join with condition

提问by stej

Suppose I have these tables

假设我有这些表

create table bug (
    id int primary key, 
    name varchar(20)
)
create table blocking (
    pk int primary key,
    id int, 
    name varchar(20)
)

insert into bug values (1, 'bad name')
insert into bug values (2, 'bad condition')
insert into bug values (3, 'about box')
insert into blocking values (0, 1, 'qa bug')
insert into blocking values (1, 1, 'doc bug')
insert into blocking values (2, 2, 'doc bug')

and I'd like to join the tables on idcolumns and the result should be like this:

我想加入id列上的表，结果应该是这样的：

id          name                 blockingName
----------- -------------------- --------------------
1           bad name             qa bug
2           bad condition        NULL
3           about box            NULL

This means: I'd like to return all rows from #bug there should be only 'qa bug' value in column 'blockingName' or NULL (if no matching row in #blocking was found)

这意味着：我想从#bug 返回所有行，列“blockingName”或NULL 中应该只有“qa bug”值（如果在#blocking 中找不到匹配的行）

---

---

My naive select was like this:

我天真的选择是这样的：

select * from #bug t1 
    left join #blocking t2 on t1.id = t2.id
    where t2.name is null or t2.name = 'qa bug'

but this does not work, because it seems that the condition is first applied to #blocking table and then it is joined.

但这不起作用，因为似乎条件首先应用于#blocking table，然后它被加入。

What is the simplest/typical solution for this problem? (I have a solution with nested select, but I hope there is something better)

这个问题最简单/典型的解决方案是什么？（我有一个嵌套选择的解决方案，但我希望有更好的解决方案）

回答by samjudson

Simply put the "qa bug" criteria in the join:

只需将“qa bug”标准放在连接中：

select t1.*, t2.name from #bug t1 
left join #blocking t2 on t1.id = t2.id AND t2.name = 'qa bug'

回答by ludek janicek

correct select is:

正确的选择是：

create table bug (
id int primary key, 
name varchar(20)
)
insert into bug values (1, 'bad name')
insert into bug values (2, 'bad condition')
insert into bug values (3, 'about box')

CREATE TABLE blocking
(
pk int IDENTITY(1,1)PRIMARY KEY ,
id int, 
name varchar(20)
)
insert into blocking values (1, 'qa bug')
insert into blocking values (1, 'doc bug')
insert into blocking values (2, 'doc bug')


select 
t1.id, t1.name,
(select  b.name from blocking b where b.id=t1.id and b.name='qa bug')
from bug t1 

回答by StevieG

select * 
from #bug t1 
left join #blocking t2 on t1.id = t2.id and t2.name = 'qa bug'

回答by Matt Fenwick

It looks like you want to select only one row from #blockingand join that to #bug. I would do:

看起来您只想从中选择一行#blocking并将其加入到#bug. 我会做：

select t1.id, t1.name, t2.name as `blockingName` 
from `#bug` t1
left join (select * from `#blocking` where name = "qa bug") t2
on t1.id = t2.id

回答by Diego

make sure the inner query only returns one row. You may have to add a top 1 on it if it returns more than one.

确保内部查询只返回一行。如果它返回多个，您可能需要在其上添加一个前 1。

select 
t1.id, t1.name,
(select  b.name from #blocking b where b.id=t1.id and b.name='qa bug')
from #bug t1 

回答by Jake Feasel

Here's a demo: http://sqlfiddle.com/#!2/414e6/1

这是一个演示：http: //sqlfiddle.com/#!2/414e6/1

select
  bug.id,
  bug.name,
  blocking.name as blockingType
from
  bug
    left outer join blocking on
      bug.id = blocking.id AND
      blocking.name = 'qa bug'
order by
  bug.id

By adding the "blocking.name" clause under the left outer join, rather than to the where, you indicate that it should also be consider "outer", or optional. When part of the where clause, it is considered required (which is why the null values were being filtered out).

通过在左外连接下添加“blocking.name”子句，而不是添加到 where，您表明它也应该被视为“外连接”，或可选。当作为 where 子句的一部分时，它被认为是必需的（这就是空值被过滤掉的原因）。

BTW - sqlfiddle.com is my site.

顺便说一句 - sqlfiddle.com 是我的网站。