/*
Alias: WORK
LiveAnswer: FALSE

*/

SELECT Suppliers.SupplierID, DentEconomics.DentistID, SUM(DentEconomics.Credit) AS Total, SUM(DentEconomics.CreditEURO) AS TotalEURO
FROM Suppliers LEFT OUTER JOIN DentEconomics ON Suppliers.SupplierID = DentEconomics.SupplierID
WHERE DentEconomics.sign = 1 AND DentEconomics.TransactionType = 0
GROUP BY Suppliers.SupplierID, DentEconomics.DentistID
UNION 
SELECT Suppliers.SupplierID, DentEconomics.DentistID, SUM(-DentEconomics.Credit) AS Total, SUM(-DentEconomics.CreditEURO) AS TotalEURO
FROM Suppliers LEFT OUTER JOIN DentEconomics ON Suppliers.SupplierID = DentEconomics.SupplierID
WHERE DentEconomics.sign = -1 AND DentEconomics.TransactionType = 0
GROUP BY Suppliers.SupplierID, DentEconomics.DentistID
UNION                                                               
SELECT Suppliers.SupplierID, 0 AS DentistID, 0 AS Total, 0 AS TotalEURO
FROM Suppliers 
WHERE Suppliers.SupplierID NOT IN 	(SELECT DentEconomics.SupplierID
												FROM DentEconomics
												WHERE DentEconomics.TransactionType = 0)
GROUP BY Suppliers.SupplierID
