AVI AVI - 1 month ago 9
SQL Question

Can anyone help me to get the query to retrieve 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


Output should be:

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))