That worked really good.

But I couldn't get it to work with a many to many table in the middle

Here is the schema:

CREATE TABLE [dbo].[Dealers](

[DealerID] [int] NULL,

[DealerCode] [varchar](15) NOT NULL,

[DealerName] [varchar](50) NOT NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[Forecast](

[ForecastId] [int] NULL,

[Name] [varchar](50) NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[DealerForecastMap](

[DealerForecastMapID] [int] NULL,

[DealerID] [varchar](15) NULL,

[ForecastID] [int] NULL

) ON [PRIMARY]

INSERT Dealers(DealerID, DealerCode, DealerName) VALUES(1, '11', 'Sears')

INSERT Dealers(DealerID, DealerCode, DealerName) VALUES(2, '05', 'Pick And Save')

INSERT Dealers(DealerID, DealerCode, DealerName) VALUES(3, '08', 'Pennys')

INSERT Forecast(ForecastID, Name) VALUES(4, 'Dual')

INSERT Forecast(ForecastID, Name) VALUES(5, 'Mortgage')

INSERT Forecast(ForecastID, Name) VALUES(6, 'Retail')

INSERT Forecast(ForecastID, Name) VALUES(7, 'Wholesale')

INSERT Forecast(ForecastID, Name) VALUES(8, 'Region')

INSERT Forecast(ForecastID, Name) VALUES(9, 'Market')

INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(1, 1, 4)

INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(2, 1, 5)

INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(3, 1, 6)

INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(4, 2, 4)

INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(5, 2, 5)

INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(6, 2, 6)

INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(7, 2, 7)

INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(8, 2, 8)

INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(9, 3, 4)

SELECT d.DealerID, d.DealerCode, DealerName, f.ForecastID, Name

FROM Dealers d

JOIN DealerForecastMap dfm

ON d.DealerID = dfm.DealerID

JOIN Forecast f

ON f.ForecastId = dfm.ForecastID

And the results:

DealerIDDealerCodeDealerNameForecastIDName

111Sears4Dual

111Sears5Mortgage

111Sears6Retail

205Pick And Save4Dual

205Pick And Save5Mortgage

205Pick And Save6Retail

205Pick And Save7Wholesale

205Pick And Save8Region

308Pennys4Dual

How would I show the results with each dealer showing all the Forecasts including the forecasts each one is missing?

Thanks,

Tom