Left join with condition
Solution 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'
Solution 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
Solution 3
select *
from #bug t1
left join #blocking t2 on t1.id = t2.id and t2.name = 'qa bug'
Solution 4
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
Solution 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
stej
C#/Web developer interested mainly in PowerShell and scripting.
Updated on July 12, 2022Comments
-
stej almost 2 years
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)
-
stej over 12 yearsMySQL is preffered, but T-SQL is also OK.
-
StevieG over 12 yearswhat are you trying to achieve? Is it all bugs that either don't have blockers or do have a 'qa bug' blocker?
-
mathematical.coffee over 12 yearsFirstly your row 2 won't be returned with NULL because it has a matching row in #blocking. Secondly, what SQL are you using? You have a mysql tag but this is invalid mysql syntax (the '#'), and asides from that your query works in mysql (see here).
-
stej over 12 yearsPK is not important for me here (but added some).
-
mathematical.coffee over 12 yearsForget my first statement about row 2; I misunderstood you.
-
stej over 12 years@StevieG I need all bugs with their qa blockers if there is any (qa blocker). If there is no qa blocker, there should be NULL.
-
stej over 12 years@mathematical.coffee corrected table names
-
StevieG over 12 years@stej - so if a bug has no qa blocker, do you want to see a row for it in the results?
-
stej over 12 years@StevieG Yes, I want (that's the row with id 2 and 3)
-