I made some changes and created a couple of tables to show the issue.
Either way (change where clause or Join with the test for the DealerCode) solves the first issue. Here is the schema and inserts as well as the queries. These tables are just quick and dirty for illustrations only.
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 Sales(SalesID, DealerCode, ForecastID) VALUES(1, '11', 4)
INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(2, '11', 5)
INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(3, '11', 6)
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')
SELECT DealerCode
,s.ForecastId
,fc.ForecastId
,Name
FROM [Sales] s
RIGHT OUTER JOIN dbo.Forecast fc ON fc.ForecastId = s.ForecastId
WHERE dealercode = '11' OR dealerCode IS NULL
GROUP BY DealerCode,s.ForecastId,fc.ForecastId,Name
ORDER BY DealerCode
Results:
DealerCodeForecastIdForecastIdName
NULLNULL7Wholesale
NULLNULL8Region
NULLNULL9Market
1144Dual
1155Mortgage
1166Retail
But now if I add a few more records with a couple more Dealers, I don’t get the results I am looking for:
INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(1, '5', 4)
INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(2, '5', 5)
INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(3, '5', 6)
INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(1, '5', 7)
INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(2, '5', 8)
INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(3, '8', 4)
The new query:
SELECT DealerCode
,s.ForecastId
,fc.ForecastId
,Name
FROM [Sales] s
RIGHT OUTER JOIN dbo.Forecast fc ON fc.ForecastId = s.ForecastId
WHERE dealercode in ('5','8','11') OR dealerCode IS NULL
GROUP BY DealerCode,s.ForecastId,fc.ForecastId,Name
ORDER BY DealerCode
And the results:
DealerCodeForecastIdForecastIdName
NULLNULL9Market
1144Dual
1155Mortgage
1166Retail
544Dual
555Mortgage
566Retail
577Wholesale
588Region
844Dual
Here I have 1 null record for the whole result set but I want to show what each dealers forecast records are as well as the forecast records that each dealer doesn’t have.
So Dealer 11 should have 3 null records as before. Dealer 5 should have 1 null and Dealer 8 should have 4 null records and have them together with the dealer.
Something like:
DealerCodeForecastIdForecastIdName
NULLNULL7Wholesale
NULLNULL8Region
NULLNULL9Market
1144Dual
1155Mortgage
1166Retail
NULLNULL9Market
544Dual
555Mortgage
566Retail
577Wholesale
588Region
NULLNULL7Wholesale
NULLNULL8Region
NULLNULL9Market
NULLNULL8Region
844Dual
Thanks,
Tom