有条件的左连接 [英] Left join with condition

问题描述

假设我有这些表

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')

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

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

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

这意味着: 我想从#bug返回所有行 在"blockingName"列中应该只有"qa bug"值，或者为NULL(如果在#blocking中找不到匹配的行)

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)

我天真的选择是这样的:

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'

但是这不起作用，因为似乎先将条件应用于#blocking表，然后将其联接.

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

此问题最简单/典型的解决方案是什么? (我有一个嵌套选择的解决方案，但我希望有更好的东西)

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

推荐答案

只需在联接中放入"qa bug"条件:

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

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

这篇关于有条件的左连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！