/*
Alias: WORK
LiveAnswer: FALSE

*/

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