VendorId(int) VendorName(varchar) ---------------------------- 1 Nirav 2 Axay 3 Jay 4 Rajiv 5 Urvish
VendorId(int) Amount(int) ToCompany(int) Is Paid(bit) -------------------------------------------------- 1 1000 500 FALSE 1 2850 852 FALSE 1 3508 963 TRUE 2 2587 254 TRUE 2 1234 212 FALSE 2 1000 321 FALSE 2 5000 654 TRUE
VendorId Not Paid ------------------------------------------------------------------- 1 total of all ToCompany where isPaid=False and vendorid=1 2 total of all ToCompany where isPaid=False and vendorid=2
SELECT v.VendorName, SUM(COALESCE(t.Amount,0)) AS TotalAmount FROM Vendor V LEFT JOIN TXN t ON v.VendorId = t.VendorId AND t.isPaid = false GROUP BY v.VendorName
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)