The closest I can come is this: -
SELECT s.DealerCode, s.ForecastID, fc.ForecastId, fc.Name
FROM [dbo].[Forecast] fc
OUTER APPLY (SELECT code
FROM (VALUES('5'),('8'),('11'))a(code)
) c
LEFT OUTER JOIN [dbo].[Sales] s ON s.DealerCode = c.code AND s.ForecastID = fc.ForecastID
ORDER BY fc.Name;
I don't fully understand your requirements. If what you want is to have 1 record per dealer whether or not they have a forecast and you're filtering to three dealer codes then surely the most you can have is 3 records per forecast?
Here's the sample data I used (edited to make it easier to read for me): -
SET NOCOUNT ON;
--Create tables
CREATE TABLE [dbo].[Sales] ([SalesID] [int] NULL, [DealerCode] [varchar](15) NULL, [ForecastID] [int] NULL) ON [PRIMARY];
CREATE TABLE [dbo].[Forecast] ([ForecastId] [int] NULL, [Name] [varchar](50) NULL) ON [PRIMARY];
--Insert sample data
INSERT INTO [dbo].[Sales]
SELECT SalesID, DealerCode, ForecastID
FROM (VALUES(1, '11', 4),(2, '11', 5),(3, '11', 6),(1, '5', 4),
(2, '5', 5),(3, '5', 6),(1, '5', 7),(2, '5', 8),
(3, '8', 4)
)a(SalesID, DealerCode, ForecastID);
INSERT INTO [dbo].[Forecast]
SELECT ForecastID, Name
FROM (VALUES(4, 'Dual'),(5, 'Mortgage'),(6, 'Retail'),(7, 'Wholesale'),
(8, 'Region'),(9, 'Market')
)a(ForecastID, Name);
--Expected result
SELECT DealerCode, SalesForecastId, ForecastForecastId, Name
FROM (VALUES(NULL,NULL,7,'Wholesale'),(NULL,NULL,8,'Region'),(NULL,NULL,9,'Market'),
(11,4,4,'Dual'),(11,5,5,'Mortgage'),(11,6,6,'Retail'),(NULL,NULL,9,'Market'),
(5,4,4,'Dual'),(5,5,5,'Mortgage'),(5,6,6,'Retail'),(5,7,7,'Wholesale'),
(5,8,8,'Region'),(NULL,NULL,7,'Wholesale'),(NULL,NULL,8,'Region'),
(NULL,NULL,9,'Market'),(NULL,NULL,8,'Region'),(8,4,4,'Dual')
)a(DealerCode, SalesForecastId, ForecastForecastId, Name)
ORDER BY Name;
Here's my attempt: -
--Actual Query
SELECT s.DealerCode, s.ForecastID, fc.ForecastId, fc.Name
FROM [dbo].[Forecast] fc
OUTER APPLY (SELECT code
FROM (VALUES('5'),('8'),('11'))a(code)
) c
LEFT OUTER JOIN [dbo].[Sales] s ON s.DealerCode = c.code AND s.ForecastID = fc.ForecastID
ORDER BY fc.Name;
Results in: -
DealerCode ForecastID ForecastId Name
--------------- ----------- ----------- --------------------------------------------------
5 4 4 Dual
8 4 4 Dual
11 4 4 Dual
NULL NULL 9 Market
NULL NULL 9 Market
NULL NULL 9 Market
5 5 5 Mortgage
NULL NULL 5 Mortgage
11 5 5 Mortgage
5 8 8 Region
NULL NULL 8 Region
NULL NULL 8 Region
5 6 6 Retail
NULL NULL 6 Retail
11 6 6 Retail
5 7 7 Wholesale
NULL NULL 7 Wholesale
NULL NULL 7 Wholesale
But you have your expected result as : -
DealerCode SalesForecastId ForecastForecastId Name
----------- --------------- ------------------ ---------
5 4 4 Dual
11 4 4 Dual
8 4 4 Dual
NULL NULL 9 Market
NULL NULL 9 Market
NULL NULL 9 Market
5 5 5 Mortgage
11 5 5 Mortgage
NULL NULL 8 Region
NULL NULL 8 Region
5 8 8 Region
NULL NULL 8 Region
5 6 6 Retail
11 6 6 Retail
NULL NULL 7 Wholesale
5 7 7 Wholesale
NULL NULL 7 Wholesale
If you can explain the expected result, I'm sure that someone will be able to help.