SQL Where 2 values one is empty

Question

I'm struggling whit some SQL code for some hours now. I'm trying to combine 2 different values in one row, but if one value is not there (so no result) there will not row at all.

To be more clear: I have a Location whit 2 different values, those are coming from two queries. That is working fine, but sometime the second query give no results (can happen, is not bad), but than also the first value is not shown.

Declare @Start datetime,
        @Ende datetime; 
SET @Start = '01.04.2015';
SET @Ende = '30.04.2015';

SELECT t1.[Location Code], CAST(t1.Umsatz as DECIMAL(18,2))as Umsatz , CAST(t2.Ersatznachweis as DECIMAL(18,2)) as Ersatznachweis
FROM (
SELECT [Location Code],  SUM(WareBrutto) AS Umsatz

FROM (SELECT  DISTINCT [Location Code], [Document No_] , WareBrutto from [Item Ledger Entry] 
      WHERE [Location Code] > '0000' and [Location Code] < '0040' and [Document Date] >= @Start and [Document Date] <= @Ende) t
GROUP BY [Location Code]) as t1,

(select [Location Code], sum([Quantity]*Bruttopreis) as Ersatznachweis  from [Item Ledger Entry] 
where [Location Code] > '0000' and [Location Code] < '0040' and [Item No_] not IN ('00009000','00009900','00009906') and Gutschrift = '1' and [Document Date] >= @Start and [Document Date] <= @Ende
Group By [Location Code]) as t2

where t1.[Location Code] = t2.[Location Code]
order by t1.[Location Code]

It is the second query that sometimes does not return a value.

 (select [Location Code], sum([Quantity]*Bruttopreis) as Ersatznachweis  from [Item Ledger Entry] 
    where [Location Code] > '0000' and [Location Code] < '0040' and [Item No_] not IN ('00009000','00009900','00009906') and Gutschrift = '1' and [Document Date] >= @Start and [Document Date] <= @Ende
    Group By [Location Code]) as t2

But that when it comes to the end and there is no result of t2.[Location code] the result of t1 is also not shown.

where t1.[Location Code] = t2.[Location Code]

I want that t2 gets a value of zero when there is no result. I tried isnull and the coalesec option but I was not able to get a decent result. It is just not there or I get error messages.

Thank in advanced...

Using Toad for SQl on a 2012 MSSQL server.

Answer 1

The problem is that the comma join and where clause you're using makes the join an inner join (Thanks to the comment from Ed B for adding details to this). In an inner join, only matching records are shown. Since there are no records in t2, nothing is matching in t1, and no records are returned. You're looking for a LEFT join, which will join any matching records from the 2nd table to the returned records from the 1st table. If nothing is in the 2nd table, you still get all of the original records from the 1st table.

I've updated your code so it uses a LEFT join, does the join in an ON statement instead of a where, and uses COALESCE to show 0 instead of NULL for records that don't match.

The following should get what 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 as DECIMAL(18,2))as Umsatz , CAST(COALESCE(t2.Ersatznachweis, 0) as DECIMAL(18,2)) as Ersatznachweis
FROM (
SELECT [Location Code],  SUM(WareBrutto) AS Umsatz

FROM (SELECT  DISTINCT [Location Code], [Document No_] , WareBrutto from [Item Ledger Entry] 
      WHERE [Location Code] > '0000' and [Location Code] < '0040' and [Document Date] >= @Start and [Document Date] <= @Ende) t
GROUP BY [Location Code]) as t1

LEFT JOIN (select [Location Code], sum([Quantity]*Bruttopreis) as Ersatznachweis  from [Item Ledger Entry] 
where [Location Code] > '0000' and [Location Code] < '0040' and [Item No_] not IN ('00009000','00009900','00009906') and Gutschrift = '1' and [Document Date] >= @Start and [Document Date] <= @Ende
Group By [Location Code]) as t2 ON t1.[Location Code] = t2.[Location Code]
order by t1.[Location Code]

Answer 2

You should use a better syntax in your statements. Use a join instead of select from 2 comma seperated tables. Then you'll see that you'll need a left join.

SELECT ... AS t1
LEFT JOIN 
(SELECT ...) AS t2 ON t1.[Location Code] = t2.[Location Code]
...