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

Question

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

It's amount (with a single m - not ammount) ...

marc_s
– marc_s

2012-08-22 17:20:27 +00:00
Commented Aug 22, 2012 at 17:20 — marc_s
– marc_s, Commented Aug 22, 2012 at 17:20

Gonzalo.- · Accepted Answer · 2012-08-22 17:29:02Z

1

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

edited Aug 22, 2012 at 17:29

answered Aug 22, 2012 at 17:15

Gonzalo.-

12.7k5 gold badges53 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related