FULL OUTER JOIN duplicating rows using COALESCE
The problem with your query isn't the use of COALESCE
, but simply with the JOIN
. There are 2 rows in the Forecast
table that have the same combination of Model, Month, Country
, rows with ID
2 and 3:
╔════╦═══════╦═════════════════════════╦═════════╦═══════╗
║ ID ║ Model ║ Month ║ Country ║ Count ║
╠════╬═══════╬═════════════════════════╬═════════╬═══════╣
║ 2 ║ ABC ║ 2014-02-01 00:00:00.000 ║ Germany ║ 1100 ║
║ 3 ║ ABC ║ 2014-02-01 00:00:00.000 ║ Germany ║ 900 ║
╚════╩═══════╩═════════════════════════╩═════════╩═══════╝
Both of them join with the row ID
3 from the Sales
table:
╔════╦═══════╦═════════════════════════╦═════════╦═══════╗
║ ID ║ Model ║ Month ║ Country ║ Count ║
╠════╬═══════╬═════════════════════════╬═════════╬═══════╣
║ 3 ║ ABC ║ 2014-02-01 00:00:00.000 ║ Germany ║ 900 ║
╚════╩═══════╩═════════════════════════╩═════════╩═══════╝
And since your query is using COALESCE(s.ID, fc.ID)
, then you get 2 rows with ID
3 in the results
Lamak's answer provides the reason for the duplicate rows in the result. Here is one solution:
WITH Sales AS
( ... )
, Forecasts AS
( ...)
, Combos AS -- get all distinct
( -- model + month + country
SELECT Model, Month, Country -- combinations
FROM Sales -- from Sales
UNION -- this is UNION DISTINCT
SELECT Model, Month, Country
FROM Forecasts -- and Forecasts
)
SELECT ID = COALESCE(s.ID, f.ID),
c.Model,
c.Month,
c.Country,
Amount = COALESCE(s.Amount, f.Amount),
[Forecast / Sales] = COALESCE(s.[Forecast / Sales],
f.[Forecast / Sales])
FROM Combos c
LEFT JOIN Sales s
ON s.Model = c.Model
AND s.Month = c.Month
AND s.Country = c.Country
LEFT JOIN Forecasts f
ON s.Model IS NULL -- join Forecasts only if there is no Sales
AND f.Model = c.Model
AND f.Month = c.Month
AND f.Country = c.Country
ORDER BY ID, Month, Country, Model ;
Test at: SQL-Fiddle
It appears you simply want to return the entire Sales
set and complement it with entries from Forecasts
that are not found in Sales
. For that, I would probably just use UNION ALL like this:
WITH Sales AS
(
...
)
, Forecasts AS
(
...
)
SELECT ID, Model, Month, Country, Amount, [Forecast / Sales]
FROM Sales
UNION ALL
SELECT ID, Model, Month, Country, Amount, [Forecast / Sales]
FROM Forecasts
WHERE NOT EXISTS
(
SELECT Model, Month, Country
INTERSECT
SELECT Model, Month, Country
FROM Sales
);