Left join with condition

All we need is an easy explanation of the problem, so here it is.

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

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'

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)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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'

Method 2

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 

Method 3

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

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

Method 4

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

Method 5

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.

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

Method 6

Here’s a demo: 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).

BTW – sqlfiddle.com is my site.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply