Menu
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)
408
The LEFT JOIN condition is used to decide how to retrieve rows from table 2nd_table. If there is a row in 1st_table that matches the WHERE clause, but there is no row in 2nd_table that matches the ON condition, an extra 2nd_table row is generated with all columns set to NULL.
The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in the right table; the join will still return a row in the result, but with NULL in each column from the right table.
If you have an OR condition in the JOIN - and there is no possibility that the values in the OR statement overlap...then you can convert it to a UNION ALL. If the values overlap it would require a UNION which may not improve performance over the JOIN.
The Left Join in SQL basically returns all records from the left table and the matched records from the right tables. For example, let's say, we have two tables, Table A and Table B. When Left Join is applied on these two tables, all records from Table A and only the matched records from Table B will be displayed.
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' If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
