The same way we've already explained.

SELECT *

FROM (SELECT DealerID, DealerCode, DealerName, ForecastId, Name

FROM Dealers d

CROSS JOIN Forecast f) df

LEFT OUTER JOIN DealerForecastMap m ON df.ForecastId = m.ForecastId AND df.DealerID = m.DealerID;

Results in: -

DealerID DealerCode DealerName ForecastId Name DealerForecastMapID DealerID ForecastId

----------- --------------- -------------------------------------------------- ----------- -------------------------------------------------- ------------------- --------------- -----------

1 11 Sears 4 Dual 1 1 4

1 11 Sears 5 Mortgage 2 1 5

1 11 Sears 6 Retail 3 1 6

1 11 Sears 7 Wholesale NULL NULL NULL

1 11 Sears 8 Region NULL NULL NULL

1 11 Sears 9 Market NULL NULL NULL

2 05 Pick And Save 4 Dual 4 2 4

2 05 Pick And Save 5 Mortgage 5 2 5

2 05 Pick And Save 6 Retail 6 2 6

2 05 Pick And Save 7 Wholesale 7 2 7

2 05 Pick And Save 8 Region 8 2 8

2 05 Pick And Save 9 Market NULL NULL NULL

3 08 Pennys 4 Dual 9 3 4

3 08 Pennys 5 Mortgage NULL NULL NULL

3 08 Pennys 6 Retail NULL NULL NULL

3 08 Pennys 7 Wholesale NULL NULL NULL

3 08 Pennys 8 Region NULL NULL NULL

3 08 Pennys 9 Market NULL NULL NULL