Microsoft SQL Server forum - Subtracting two columns from diff tables

Author

Subtracting two columns from diff tables

alomrani@gmail.com

2005-11-29, 3:23 am

Hi all,

I encountered this small problem

I have two tables A and B with two columns 1 and 2 each, I would like
the first column of each table when match the first in the second table
is to subtract the second column

so the result would look as follows

Column 1 | Columnn 2
where A1=B1 | A2-B2

Now this is no problem so far ..

But if there was no corresponding value in column 1 in either tables ..
i.e. field A1 doesnt exist in Table B column 1, IT SHALL DO A2 - 0; or
0-B2 ..

NOW How can that be achieved ?

Thanks all for your help

2005-11-29, 3:23 am

Does this work for you?

select
isnull(A.A2,0) - isnull(B.B2,0) as Diff
from
tableA A
full outer join tableB B on A.A1 = B.B1

MC
<alomrani@gmail.com> wrote in message
news:1133252899.750115.34110@o13g2000cwo.googlegroups.com...
> Hi all,
>
> I encountered this small problem
>
> I have two tables A and B with two columns 1 and 2 each, I would like
> the first column of each table when match the first in the second table
> is to subtract the second column
>
> so the result would look as follows
>
> Column 1 | Columnn 2
> where A1=B1 | A2-B2
>
> Now this is no problem so far ..
>
> But if there was no corresponding value in column 1 in either tables ..
> i.e. field A1 doesnt exist in Table B column 1, IT SHALL DO A2 - 0; or
> 0-B2 ..
>
> NOW How can that be achieved ?
>
> Thanks all for your help
>

Jens

2005-11-29, 3:23 am

Hi,

Select ISNULL(Table1.col2,0) - ISNULL(Table2.col2,0)
FROm Table1
FULL OUTER JOIN Table2
ON Table1.Col1 = Table2.Col1

HTH, Jens Suessmeyer.

alomrani@gmail.com

2005-11-30, 3:23 am

Hi all

thanks alot jens and mc i could easily figure it out.

regards

Sponsored Links