tshad (8/23/2012)

---

Actually, it isn't exactly the same way.

I was trying to figure out how to get your way to work with a middle table and couldn't get it to work.

You're way works but it is different.

In the query you have the CROSS JOIN in the Derived Table (df)

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;

In the old query, you have the CROSS JOIN outside of the Derived table (m).

SELECT *

FROM

(SELECT DealerCode FROM Sales GROUP BY DealerCode) m

CROSS JOIN Forecast fc

LEFT JOIN [Sales] s

on s.DealerCode = m.DealerCode AND s.ForecastID = fc.ForecastId

ORDER BY m.DealerCode

I am not sure of the difference but I do know I could not seem to get my query with the middle table to work as it was set up for my other example that had just 2 tables.

Not sure why yet, but will look at it more closely.

Thanks,

Tom