AVI AVI - 1 month ago 10
SQL Question

Can any one help me to get the query to retrive data like below

A B c
id sal id sal id sal
1 100 1 100 1 100
2 200 2 200 2 200
3 300 3 300
4 400


O/P

id sal
1 300
2 600
3 600
4 400

Answer

Here is the query for the exact results you are looking for.

select A.id, sum(A.sal + ISNULL(B.sal,0) + ISNULL(C.sal,0) ) sal from TableA A
left join TableB B on A.id=B.id
left join TableC C on A.id=C.id
group by A.id

Also, in case TableC or TableB were to contain records that are not in the other tables, you can use the below code:

select IsNULL(A.id,IsNULL(B.id,C.id)) as id, sum(ISNULL(A.sal,0)+ ISNULL(B.sal,0) + ISNULL(C.sal,0) ) sal from TableA A
full outer join TableB B on A.id=B.id
full outer join TableC C on A.id=C.id
group by IsNULL(A.id,IsNULL(B.id,C.id))