I have two tables.
Voucher
V_Id Cost
1 400
2 500
Expense
E_Id V_Id Cost
1 1 100
2 1 100
3 1 100
I need to join them so that I get
V_Id V_Cost E_Cost
1 400 300
2 500 0
I have tried INNER JOIN but I get the same value fro both of them. Please help.
I took the Expense table's SUM(Cost) as E_Cost, then Left join with Voucher to get the expected result:
SELECT V.V_Id, V.Cost AS V_Cost, ISNULL(E.E_Cost, 0) AS E_Cost
FROM Voucher V
LEFT JOIN ( SELECT V_Id, SUM(Cost) AS E_Cost
FROM Expense
GROUP BY V_Id ) AS E ON E.V_Id = V.V_Id
Working example with given data:
DECLARE @Voucher TABLE (V_Id INT, Cost INT)
INSERT INTO @Voucher (V_Id, Cost)
VALUES (1, 400), (2, 500)
DECLARE @Expense TABLE (E_Id INT, V_Id INT, Cost INT)
INSERT INTO @Expense (E_Id, V_Id, Cost)
VALUES (1, 1, 100), (2, 1, 100), (3, 1, 100)
SELECT V.V_Id, V.Cost AS V_Cost, ISNULL(E.E_Cost, 0) AS E_Cost
FROM @Voucher V
LEFT JOIN ( SELECT V_Id, SUM(Cost) AS E_Cost
FROM @Expense
GROUP BY V_Id ) AS E ON E.V_Id = V.V_Id
