sql server - SQL Where 2 values one is empty -

i'm struggling whit sql code hours now. i'm trying combine 2 different values in 1 row, if 1 value not there (so no result) there not row @ all.

to more clear: have location whit 2 different values, coming 2 queries. working fine, sometime second query give no results (can happen, not bad), first value not shown.

declare @start datetime,         @ende datetime;  set @start = '01.04.2015'; set @ende = '30.04.2015';  select t1.[location code], cast(t1.umsatz decimal(18,2))as umsatz , cast(t2.ersatznachweis decimal(18,2)) ersatznachweis ( select [location code],  sum(warebrutto) umsatz  (select  distinct [location code], [document no_] , warebrutto [item ledger entry]        [location code] > '0000' , [location code] < '0040' , [document date] >= @start , [document date] <= @ende) t group [location code]) t1,  (select [location code], sum([quantity]*bruttopreis) ersatznachweis  [item ledger entry]  [location code] > '0000' , [location code] < '0040' , [item no_] not in ('00009000','00009900','00009906') , gutschrift = '1' , [document date] >= @start , [document date] <= @ende group [location code]) t2  t1.[location code] = t2.[location code] order t1.[location code] 

it second query not return value.

 (select [location code], sum([quantity]*bruttopreis) ersatznachweis  [item ledger entry]      [location code] > '0000' , [location code] < '0040' , [item no_] not in ('00009000','00009900','00009906') , gutschrift = '1' , [document date] >= @start , [document date] <= @ende     group [location code]) t2 

but when comes end , there no result of t2.[location code] result of t1 not shown.

where t1.[location code] = t2.[location code] 

i want t2 gets value of 0 when there no result. tried isnull , coalesec option not able decent result. not there or error messages.

thank in advanced...

using toad sql on 2012 mssql server.

the problem comma join , clause you're using makes join inner join (thanks comment ed b adding details this). in inner join, matching records shown. since there no records in t2, nothing matching in t1, , no records returned. you're looking left join, join matching records 2nd table returned records 1st table. if nothing in 2nd table, still of original records 1st table.

i've updated code uses left join, join in on statement instead of where, , uses coalesce show 0 instead of null records don't match.

the following should you're looking for:

declare @start datetime,         @ende datetime;  set @start = '01.04.2015'; set @ende = '30.04.2015';  select t1.[location code], cast(t1.umsatz decimal(18,2))as umsatz , cast(coalesce(t2.ersatznachweis, 0) decimal(18,2)) ersatznachweis ( select [location code],  sum(warebrutto) umsatz  (select  distinct [location code], [document no_] , warebrutto [item ledger entry]        [location code] > '0000' , [location code] < '0040' , [document date] >= @start , [document date] <= @ende) t group [location code]) t1  left join (select [location code], sum([quantity]*bruttopreis) ersatznachweis  [item ledger entry]  [location code] > '0000' , [location code] < '0040' , [item no_] not in ('00009000','00009900','00009906') , gutschrift = '1' , [document date] >= @start , [document date] <= @ende group [location code]) t2 on t1.[location code] = t2.[location code] order t1.[location code]