Left Table full and right table data if exists

Question

Hi
I have two table like below

create table ##tab1 (AccNo nvarchar(10), tab1InvNo nvarchar(10), tab1Qty int)
create table ##tab2 (AccNo nvarchar(10), tab2InvNo nvarchar(10), tab2Qty int)
create table ##Result (AccNo nvarchar(10), tab1InvNo nvarchar(10), tab1Qty int, tab2InvNo nvarchar(10), tab2Qty int)
insert into ##tab1 values (1001, 2001, 1)
insert into ##tab1 values (1001, 2002, 2)
insert into ##tab1 values (1001, 2003, 5)

insert into ##tab2 values (1001, 2001, 1)

select * from ##tab1
select * from ##tab2

I need the result like below
Left table full data and right table data if exists

create table ##Result (AccNo nvarchar(10), tab1InvNo nvarchar(10), tab1Qty int, tab2InvNo nvarchar(10), tab2Qty int)
insert into ##Result values (1001,2001,1,2001,1)
insert into ##Result values (1001,2002,2,'',0)
insert into ##Result values (1001,2003,5,'',0)
select * from ##Result

Thanks in Advance

Answer 1

Try it with an OUTER JOIN and the ISNULL function, like

SELECT T1.*, ISNULL(T2.tab2InvNo) AS tab2InvNo, ISNULL(T2.tab2Qty, 0) AS tab2Qty
FROM ##tab1 AS T1
     LEFT JOIN
     ##tab2 AS T2
         ON T1.AccNo = T2.AccNo
            AND T1.tab1InvNo = T2.tab2InvNo