CODEWITHSUNDEEP
sqlsql-servernull
AntoonVs
AntoonVs

Reputation: 91

SQL Server query get rows table 1 - rows table 2 without null value

select 
    id, amount - (select sum(amount) as amount 
                  from table2 
                  where column = 'value' and table1.id = table2.id 
                  group by table2.id) as amount
from 
    table1 
order by 
    table1.id

The result is all values from table1 - amount from table2 except the table1.id's that are not in table2.id's they get null value.

So it's all good except that I have the null values because I need these to be the table1.amount value

Also tried stuff like this but still not working

select 
     id, amount - isnull((select sum(amount) as amount 
                          from table2 
                          where column = 'value' and table1.id = table2.id 
                          group by table2.id), table1.amount) as amount
from 
     table1 
order by 
     table1.id

Then I get 0.00 but the table1.id's that have null in result set do have a real amount value in table1.amount

Overview of what I need 

table 1   values    (1,12 ; 2,27 ; 3,9) table 2   values    (1,9 ; 3,12) result table (1,3 ; 2,27 ; 3,-3)

so I need table1 - values from table 2 to get result table

Upvotes: 0

Views: 577

Answers (1)

Gonzalo.-
Gonzalo.-

Reputation: 12672

better do this

select
t1.id, t1.amount - isnull(t2.amountTable2,0) as 'amount'
from table1 T1
left join (select id, sum(ammunt) as 'amountTable2' from table2 group by Id) T2
       on t1.id = t2.id
order by t1.id

this will get the total Sum of each id in table2 then will join by id, and do the difference. If there is no id in table2, will use 0

That answer consider (I thougth that at first) that t2.Id could be repeated If it's unique, remove the group by 

select
    t1.id, t1.amount - isnull(t2.amount,0) as 'Totalamount'
    from table1 T1
    left join table2 T2
           on t1.id = t2.id
    order by t1.id

Upvotes: 1

Related Questions