[SOLVED] Filter rows in SQL query when a subquery returns null

Filter rows in SQL query when a subquery returns null

I have a problem with subqueries. I'd like to select rows which contain a sum from a different table - so far no problem. However I'd like to filter for rows that don't have sum.

Example: Table1 has only an Id column, Table2 has an Id and a value column - all of datatype int.

Now I want to see all entries from Table1 and calculate the sum for that Id from Table2.

My query is very simple:

select 
    Id,
    (Select sum(Value) 
     from Table2 
     where Table1.Id = Table2.Id) as Total
from
    Table1;

However in Table2 there are only entries for Id = 1 and Id = 3. Since my query returns values for all three rows (showing NULL for Id = 2), I'd like to suppress the row for Id = 2.

I tried

select 
    Id,
    (select sum(Value) 
     from Table2 
     where Table1.Id = Table2.Id) as Total
from
    Table1
where 
    not (Total is null);

Unfortunately this is not allowed in SQL since I cannot access the value for Total.

What would be the best and easiest way to achieve this?

Solution

You can only use/reference a 'calculated' column in the 'order by'.

Here are two options:

-- Option 1: Add the calculation in the WHERE clause
select 
[Id], 
(select sum([value]) from Table2 where Table1.[Id] = Table2.[Id]) as Total 
from 
Table1 
where 
(select sum([value]) from Table2 where Table1.[Id] = Table2.[Id]) is not NULL;

-- Option 2: Make the orginal query a subquery
select [Id], [Total] from 
(
select 
[Id], 
(select sum([value]) from Table2 where Table1.[Id] = Table2.[Id]) as Total 
from 
Table1 
) subquery where [Total] is not NULL;