Левое соединение с условием

Предположим, что у меня есть эти таблицы

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, и результат должен выглядеть примерно так:

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

Это означает: Я хотел бы вернуть все строки из #bug должно быть только значение "qa bug" в столбце "blockingName" или NULL (если не найдена соответствующая строка в #blocking)

Мой наивный выбор был таким:

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

но это не сработает, потому что кажется, что условие сначала применяется к таблице #blocking, а затем к нему присоединяется.

Какое простейшее/типичное решение этой проблемы? (У меня есть решение с вложенным select, но я надеюсь, что есть что-то лучше)

Ответ 1

Просто поставьте критерии "qa bug" в соединении:

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

Ответ 2

правильный выбор:

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

Ответ 3

Похоже, вы хотите выбрать только одну строку из #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

Ответ 4

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

Ответ 5

убедитесь, что внутренний запрос возвращает только одну строку. Возможно, вам придется добавить верхнюю часть 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

Ответ 6

Вот демо: 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

Добавив предложение "blocking.name" под левым внешним соединением, а не к тому месту, вы указываете, что оно также должно считаться "внешним" или необязательным. Когда часть предложения where считается считанной (поэтому нулевые значения отфильтровываются).

BTW - sqlfiddle.com - это мой сайт.