InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 16:01:50 GMT20http://www.sqlservercentral.com/Forums/Topic1347538-392-1.aspxLooks good.Thanks,TomFri, 14 Sep 2012 22:24:39 GMTtshadhttp://www.sqlservercentral.com/Forums/Topic1347538-392-1.aspxLooks fine to me, Tom. It's important that you don't have dupes in the matrix table source (the CROSS JOIN bit), if the two columns returned are the PK's of the two tables then you're ok. If they are not, then wedge in a GROUP BY:[code="sql"]SELECT mdf.*FROM ( SELECT f.DealerForecastId, m.ModelID FROM DealerForecast f CROSS JOIN Models m WHERE m.IsActive = 1 GROUP BY f.DealerForecastId, m.ModelID) mdfLEFT OUTER JOIN DealerForecastModel dfmON mdf.DealerForecastId = dfm.DealerForecastId AND mdf.ModelID = dfm.ModelIDWHERE dfm.DealerForecastModelID IS NULL[/code]Fri, 07 Sep 2012 02:38:37 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1347538-392-1.aspxHi Chris,One other small question.With the Cross Join there is "ON" part.What if the Model table had a column "IsActive" which is a 0 or 1 and I wanted only those parts of the table where the records were active (IsActive = 1).I could do something like:SELECT *FROM (SELECT *FROM DealerForecast fCROSS JOIN Models m) mdfLEFT OUTER JOIN DealerForecastModel dfmON mdf.DealerForecastId = dfm.DealerForecastId AND mdf.ModelID = dfm.ModelIDWHERE DealerForecastModelID IS NULL and IsActive = 1But that would be after the whole process is done.To be more efficient, I would like to do the filtering in the sub query so the these records are filtered out at the beginning before Left Join.How best would I do that?I could do something like this (I think):SELECT *FROM (SELECT *FROM DealerForecast fCROSS JOIN (select * from Models where IsActive = 1) m) mdfLEFT OUTER JOIN DealerForecastModel dfmON mdf.DealerForecastId = dfm.DealerForecastId AND mdf.ModelID = dfm.ModelIDWHERE DealerForecastModelID IS NULLIs that the best way?Thanks,TomThu, 06 Sep 2012 15:18:08 GMTtshadhttp://www.sqlservercentral.com/Forums/Topic1347538-392-1.aspxLooks fine to me, Tom. Thanks for the feedback.Thu, 06 Sep 2012 02:27:14 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1347538-392-1.aspxI got it to work with the new scenario. I wasn't sure how to deal with multiple mapping tables but realized that it really is no different.For the new scema I did the following query with an insert where the "WHERE" clause inserts only those not already in the table. Worked perfectly.I knew what a CROSS JOIN was but had always wondered why you would ever need or use one. And know I see a use for it.I was also trying to figure out which table to put the cross join on and then realized that it didn't make any difference.So the thought process is:1) Do a join to get all the possible records2) Left Join the table you want the records to go into3) Filter out the non nullsSELECT *FROM (SELECT * FROM DealerForecast f CROSS JOIN Models m) mdfLEFT OUTER JOIN DealerForecastModel dfmON mdf.DealerForecastId = dfm.DealerForecastId AND mdf.ModelID = dfm.ModelIDWHERE DealerForecastModelID IS NULLThe Schema I used was:[code]ALTER PROC [dbo].[CreateSchema]ASIF (EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'DealerForecastModel')) DROP Table DealerForecastModelIF (EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'DealerForecast')) DROP Table DealerForecastIF (EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'ModelForecast')) DROP Table ModelForecastIF (EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Dealers')) DROP Table DealersIF (EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Models')) DROP Table ModelsIF (EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Forecast')) DROP Table ForecastCREATE TABLE [dbo].[Dealers]([DealerID] [int] CONSTRAINT PK_Dealers Primary Key,[DealerCode] [varchar](15) NOT NULL,[DealerName] [varchar](50) NOT NULL) ON [PRIMARY]CREATE TABLE [dbo].[Models]([ModelID] [int] CONSTRAINT PK_Models Primary Key,[ModelCode] [varchar](15) NOT NULL,) ON [PRIMARY]CREATE TABLE [dbo].[Forecast]([ForecastId] [int] CONSTRAINT PK_Forecast Primary Key,[Name] [varchar](50) NULL) ON [PRIMARY]CREATE TABLE [dbo].[DealerForecast]([DealerForecastID] [int] CONSTRAINT PK_DealerForecast Primary Key,[DealerID] [int] NULL,[ForecastID] [int] NULL) ON [PRIMARY]CREATE TABLE [dbo].[ModelForecast]([ModelForecastID] [int] CONSTRAINT PK_ModelForecast Primary Key,[ModelID] [int] NULL,[ForecastID] [int] NULL) ON [PRIMARY]CREATE TABLE [dbo].[DealerForecastModel]([DealerForecastModelID] [int] CONSTRAINT PK_DealarForecastModel Primary Key,[DealerForecastID] [int] NULL,[ModelID] [int] NULL) ON [PRIMARY]ALTER TABLE [dbo].[DealerForecastModel] WITH CHECK ADD CONSTRAINT [FK_DealerForecastModel_DealerForecast] FOREIGN KEY([DealerForecastID])REFERENCES [dbo].[DealerForecast] ([DealerForecastID])ALTER TABLE [dbo].[DealerForecastModel] WITH CHECK ADD CONSTRAINT [FK_DealerForecastModel_Models] FOREIGN KEY([ModelID])REFERENCES [dbo].[Models] ([ModelID])ALTER TABLE [dbo].[DealerForecast] WITH CHECK ADD CONSTRAINT [FK_DealerForecast_Dealers] FOREIGN KEY([DealerID])REFERENCES [dbo].[Dealers] ([DealerID])ALTER TABLE [dbo].[DealerForecast] WITH CHECK ADD CONSTRAINT [FK_DealerForecast_Forecast] FOREIGN KEY([ForecastID])REFERENCES [dbo].[Forecast] ([ForecastId])ALTER TABLE [dbo].[ModelForecast] WITH CHECK ADD CONSTRAINT [FK_ModelForecast_Forecast] FOREIGN KEY([ForecastID])REFERENCES [dbo].[Forecast] ([ForecastId])ALTER TABLE [dbo].[ModelForecast] WITH CHECK ADD CONSTRAINT [FK_ModelForecast_Models] FOREIGN KEY([ModelID])REFERENCES [dbo].[Models] ([ModelID])INSERT Dealers(DealerID, DealerCode, DealerName) VALUES(1, '11', 'Brea')INSERT Dealers(DealerID, DealerCode, DealerName) VALUES(2, '05', 'Fontana')INSERT Dealers(DealerID, DealerCode, DealerName) VALUES(3, '08', 'Cerritos')INSERT Models(ModelID, ModelCode) VALUES(1, 'YZ-125')INSERT Models(ModelID, ModelCode) VALUES(2, 'YZ-250')INSERT Models(ModelID, ModelCode) VALUES(3, 'YZ-450')INSERT Models(ModelID, ModelCode) VALUES(4, 'Rhino 700')INSERT Models(ModelID, ModelCode) VALUES(5, 'Grizzly 450')INSERT Models(ModelID, ModelCode) VALUES(6, 'FZR')INSERT Models(ModelID, ModelCode) VALUES(7, 'FZS')INSERT Forecast(ForecastID, Name) VALUES(4, 'Motocross')INSERT Forecast(ForecastID, Name) VALUES(5, 'ATV')INSERT Forecast(ForecastID, Name) VALUES(6, 'Watercraft')INSERT ModelForecast(ModelForecastID, ModelID, ForecastID) VALUES(1, 1, 4)INSERT ModelForecast(ModelForecastID, ModelID, ForecastID) VALUES(2, 2, 4)INSERT ModelForecast(ModelForecastID, ModelID, ForecastID) VALUES(3, 3, 4)INSERT ModelForecast(ModelForecastID, ModelID, ForecastID) VALUES(4, 4, 5)INSERT ModelForecast(ModelForecastID, ModelID, ForecastID) VALUES(5, 5, 5)INSERT ModelForecast(ModelForecastID, ModelID, ForecastID) VALUES(6, 6, 6)INSERT ModelForecast(ModelForecastID, ModelID, ForecastID) VALUES(7, 7, 6)INSERT DealerForecast(DealerForecastID, DealerID, ForecastID) VALUES(1, 1, 4)INSERT DealerForecast(DealerForecastID, DealerID, ForecastID) VALUES(2, 1, 5)INSERT DealerForecast(DealerForecastID, DealerID, ForecastID) VALUES(3, 1, 6)INSERT DealerForecast(DealerForecastID, DealerID, ForecastID) VALUES(4, 2, 4)INSERT DealerForecast(DealerForecastID, DealerID, ForecastID) VALUES(5, 2, 5)INSERT DealerForecast(DealerForecastID, DealerID, ForecastID) VALUES(6, 2, 6)INSERT DealerForecast(DealerForecastID, DealerID, ForecastID) VALUES(7, 3, 4)INSERT DealerForecast(DealerForecastID, DealerID, ForecastID) VALUES(8, 3, 5)INSERT DealerForecast(DealerForecastID, DealerID, ForecastID) VALUES(9, 3, 6)INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(1, 1, 1)INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(2, 1, 3)INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(3, 2, 1)INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(4, 2, 2)INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(5, 2, 6)INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(6, 3, 3)INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(7, 3, 4)INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(8, 4, 6)INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(9, 5, 2)INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(10, 5, 4)INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(11, 5, 6)INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(12, 5, 7)[/code]Thanks for all the help,TomWed, 05 Sep 2012 11:40:04 GMTtshadhttp://www.sqlservercentral.com/Forums/Topic1347538-392-1.aspxI agree and have been putting a different schema and data that is really close to what I am trying to actually do.The other examples helped me to better see how it is done.I am trying to get it to work myself before posting so as to not waste anyones time.I entails using two mapping tables (many to many tables).Thanks,TomFri, 31 Aug 2012 15:16:23 GMTtshadhttp://www.sqlservercentral.com/Forums/Topic1347538-392-1.aspx[quote][b]tshad (8/23/2012)[/b][hr]Hi Cadavre,No problem.I understand what Chris had explained about the CROSS JOIN. I was trying apply that to the situation I have which I couldn't make work so I have been trying to start from the basic layout and add complexity to it to understand the process better.I have one more scenario that I am trying to put together that actually is one more table past this that is pretty much what my actual layout is like.I am trying to see if I can make it work first before I add the extra table to the sample to see if I can make it work.Thanks,Tom[/quote]Can you post it here Tom with DDL and perhaps some sample data? It's time we put this one to bed ;-)Fri, 24 Aug 2012 02:18:17 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1347538-392-1.aspxHi Cadavre,No problem.I understand what Chris had explained about the CROSS JOIN. I was trying apply that to the situation I have which I couldn't make work so I have been trying to start from the basic layout and add complexity to it to understand the process better.I have one more scenario that I am trying to put together that actually is one more table past this that is pretty much what my actual layout is like.I am trying to see if I can make it work first before I add the extra table to the sample to see if I can make it work.Thanks,TomThu, 23 Aug 2012 11:43:00 GMTtshadhttp://www.sqlservercentral.com/Forums/Topic1347538-392-1.aspx[quote][b]tshad (8/23/2012)[/b][hr]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.[/quote]You've got a little defensive there and having re-read what I wrote I can see why. I hadn't intended what I wrote to sound like I was accusing you of not paying attention, I simply wanted to draw your attention to the fact that the logic behind the idea is identical in both your previous question and your new question. As Chris explained (far better than I), the difference in the structure of the query is to accommodate the changes to the schema that you supplied with your question.Thu, 23 Aug 2012 02:40:07 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1347538-392-1.aspx[quote][b]tshad (8/23/2012)[/b][hr]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) dfLEFT 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) mCROSS JOIN Forecast fcLEFT JOIN [Sales] s on s.DealerCode = m.DealerCode AND s.ForecastID = fc.ForecastId ORDER BY m.DealerCodeI 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[/quote]Hi TomIn both queries the intent is to build an intermediate table which is a cross of DealerID and ForecastID, to which you can LEFT JOIN other tables. This ensures that you're starting with the whole picture - every dealer has every forecast.The structural difference between the two queries is to accommodate the changes in the sample tables and data you've provided.Thu, 23 Aug 2012 02:18:12 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1347538-392-1.aspxActually, 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) dfLEFT 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) mCROSS JOIN Forecast fcLEFT JOIN [Sales] s on s.DealerCode = m.DealerCode AND s.ForecastID = fc.ForecastId ORDER BY m.DealerCodeI 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,TomThu, 23 Aug 2012 02:01:13 GMTtshadhttp://www.sqlservercentral.com/Forums/Topic1347538-392-1.aspxThe same way we've already explained.[code="sql"]SELECT *FROM (SELECT DealerID, DealerCode, DealerName, ForecastId, Name FROM Dealers d CROSS JOIN Forecast f) dfLEFT OUTER JOIN DealerForecastMap m ON df.ForecastId = m.ForecastId AND df.DealerID = m.DealerID;[/code]Results in: -[code="plain"]DealerID DealerCode DealerName ForecastId Name DealerForecastMapID DealerID ForecastId----------- --------------- -------------------------------------------------- ----------- -------------------------------------------------- ------------------- --------------- -----------1 11 Sears 4 Dual 1 1 41 11 Sears 5 Mortgage 2 1 51 11 Sears 6 Retail 3 1 61 11 Sears 7 Wholesale NULL NULL NULL1 11 Sears 8 Region NULL NULL NULL1 11 Sears 9 Market NULL NULL NULL2 05 Pick And Save 4 Dual 4 2 42 05 Pick And Save 5 Mortgage 5 2 52 05 Pick And Save 6 Retail 6 2 62 05 Pick And Save 7 Wholesale 7 2 72 05 Pick And Save 8 Region 8 2 82 05 Pick And Save 9 Market NULL NULL NULL3 08 Pennys 4 Dual 9 3 43 08 Pennys 5 Mortgage NULL NULL NULL3 08 Pennys 6 Retail NULL NULL NULL3 08 Pennys 7 Wholesale NULL NULL NULL3 08 Pennys 8 Region NULL NULL NULL3 08 Pennys 9 Market NULL NULL NULL[/code]Thu, 23 Aug 2012 01:29:02 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1347538-392-1.aspxThat worked really good.But I couldn't get it to work with a many to many table in the middleHere 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, NameFROM Dealers dJOIN DealerForecastMap dfmON d.DealerID = dfm.DealerIDJOIN Forecast fON f.ForecastId = dfm.ForecastIDAnd the results:[code]DealerID DealerCode DealerName ForecastID Name1 11 Sears 4 Dual1 11 Sears 5 Mortgage1 11 Sears 6 Retail2 05 Pick And Save 4 Dual2 05 Pick And Save 5 Mortgage2 05 Pick And Save 6 Retail2 05 Pick And Save 7 Wholesale2 05 Pick And Save 8 Region3 08 Pennys 4 Dual[/code]How would I show the results with each dealer showing all the Forecasts including the forecasts each one is missing?Thanks,TomThu, 23 Aug 2012 00:22:14 GMTtshadhttp://www.sqlservercentral.com/Forums/Topic1347538-392-1.aspx[code="sql"]SELECT * FROM (SELECT DealerCode FROM Sales GROUP BY DealerCode) mCROSS JOIN Forecast fcLEFT JOIN [Sales] s on s.DealerCode = m.DealerCode AND s.ForecastID = fc.ForecastId ORDER BY m.DealerCode[/code]Tue, 21 Aug 2012 05:58:37 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1347538-392-1.aspxThe closest I can come is this: -[code="sql"]SELECT s.DealerCode, s.ForecastID, fc.ForecastId, fc.NameFROM [dbo].[Forecast] fcOUTER APPLY (SELECT code FROM (VALUES('5'),('8'),('11'))a(code) ) cLEFT OUTER JOIN [dbo].[Sales] s ON s.DealerCode = c.code AND s.ForecastID = fc.ForecastIDORDER BY fc.Name;[/code]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): -[code="sql"]SET NOCOUNT ON;--Create tablesCREATE 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 dataINSERT INTO [dbo].[Sales]SELECT SalesID, DealerCode, ForecastIDFROM (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, NameFROM (VALUES(4, 'Dual'),(5, 'Mortgage'),(6, 'Retail'),(7, 'Wholesale'), (8, 'Region'),(9, 'Market') )a(ForecastID, Name); --Expected resultSELECT DealerCode, SalesForecastId, ForecastForecastId, NameFROM (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;[/code]Here's my attempt: -[code="sql"]--Actual QuerySELECT s.DealerCode, s.ForecastID, fc.ForecastId, fc.NameFROM [dbo].[Forecast] fcOUTER APPLY (SELECT code FROM (VALUES('5'),('8'),('11'))a(code) ) cLEFT OUTER JOIN [dbo].[Sales] s ON s.DealerCode = c.code AND s.ForecastID = fc.ForecastIDORDER BY fc.Name;[/code]Results in: -[code="plain"]DealerCode ForecastID ForecastId Name--------------- ----------- ----------- --------------------------------------------------5 4 4 Dual8 4 4 Dual11 4 4 DualNULL NULL 9 MarketNULL NULL 9 MarketNULL NULL 9 Market5 5 5 MortgageNULL NULL 5 Mortgage11 5 5 Mortgage5 8 8 RegionNULL NULL 8 RegionNULL NULL 8 Region5 6 6 RetailNULL NULL 6 Retail11 6 6 Retail5 7 7 WholesaleNULL NULL 7 WholesaleNULL NULL 7 Wholesale[/code]But you have your expected result as : -[code="plain"]DealerCode SalesForecastId ForecastForecastId Name----------- --------------- ------------------ ---------5 4 4 Dual11 4 4 Dual8 4 4 DualNULL NULL 9 MarketNULL NULL 9 MarketNULL NULL 9 Market5 5 5 Mortgage11 5 5 MortgageNULL NULL 8 RegionNULL NULL 8 Region5 8 8 RegionNULL NULL 8 Region5 6 6 Retail11 6 6 RetailNULL NULL 7 Wholesale5 7 7 WholesaleNULL NULL 7 Wholesale[/code]If you can explain the expected result, I'm sure that someone will be able to help.Tue, 21 Aug 2012 02:40:43 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1347538-392-1.aspxI 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 NULLGROUP BY DealerCode,s.ForecastId,fc.ForecastId,NameORDER BY DealerCodeResults:[code]DealerCode ForecastId ForecastId NameNULL NULL 7 WholesaleNULL NULL 8 RegionNULL NULL 9 Market11 4 4 Dual11 5 5 Mortgage11 6 6 Retail[/code]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 NULLGROUP BY DealerCode,s.ForecastId,fc.ForecastId,NameORDER BY DealerCodeAnd the results:[code]DealerCode ForecastId ForecastId NameNULL NULL 9 Market11 4 4 Dual11 5 5 Mortgage11 6 6 Retail5 4 4 Dual5 5 5 Mortgage5 6 6 Retail5 7 7 Wholesale5 8 8 Region8 4 4 Dual[/code]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:[code]DealerCode ForecastId ForecastId NameNULL NULL 7 WholesaleNULL NULL 8 RegionNULL NULL 9 Market11 4 4 Dual11 5 5 Mortgage11 6 6 RetailNULL NULL 9 Market5 4 4 Dual5 5 5 Mortgage5 6 6 Retail5 7 7 Wholesale5 8 8 RegionNULL NULL 7 WholesaleNULL NULL 8 RegionNULL NULL 9 MarketNULL NULL 8 Region8 4 4 Dual[/code]Thanks,TomTue, 21 Aug 2012 01:06:41 GMTtshadhttp://www.sqlservercentral.com/Forums/Topic1347538-392-1.aspxOk..so if am not wrong then dealer code is in the first table and you are looking for all the records in second table.If this is the case then I am confused by your statement.As the extra records do not belongs to any dealer code these are records(having dealer code as NULL) which do not found any match in first table.Mon, 20 Aug 2012 22:10:09 GMTrhythmkhttp://www.sqlservercentral.com/Forums/Topic1347538-392-1.aspx[quote][b]tshad (8/20/2012)[/b][hr]Actually, this is a stripped down version of the actual query I was using so it is a little difficult to do that.But I did figure out my issue, just not sure how to deal with it.The where clause is causing the problem. The Join is done fine but the the where clause filters out anything that doesn't have DealerCode ="11".The lines that have the null for a DealerCode, which the extra lines would have, were also filtered out.I needed to change Where clause to:WHERE dealercode = '11' OR dealerCode IS NULL.That works fine with one dealercode but if I have multiple dealercodes, it doesn't work as I get 3 records even if another dealercode is missing others so I can't tell which dealercode the extra records belong to.Thanks,Tom[/quote]Put this, dealercode = '11' , in with the join criteria for the right outer join instead of in the WHERE clause. Let us know how that works. Mon, 20 Aug 2012 21:59:15 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1347538-392-1.aspxActually, this is a stripped down version of the actual query I was using so it is a little difficult to do that.But I did figure out my issue, just not sure how to deal with it.The where clause is causing the problem. The Join is done fine but the the where clause filters out anything that doesn't have DealerCode ="11".The lines that have the null for a DealerCode, which the extra lines would have, were also filtered out.I needed to change Where clause to:WHERE dealercode = '11' OR dealerCode IS NULL.That works fine with one dealercode but if I have multiple dealercodes, it doesn't work as I get 3 records even if another dealercode is missing others so I can't tell which dealercode the extra records belong to.Thanks,TomMon, 20 Aug 2012 21:37:12 GMTtshadhttp://www.sqlservercentral.com/Forums/Topic1347538-392-1.aspx[quote][b]tshad (8/20/2012)[/b][hr]I have 2 tables in my database and am RIGHT JOINING the tables and it is acting like an inner joinSELECT [DealerCode] ,iis.[ForecastSegmentId] ,fs.ForecastSegmentId ,[Name] FROM [dbo].[Sales] iis RIGHT OUTER JOIN dbo.Forecast fs ON fs.ForecastId = iis.ForecastId WHERE dealercode = '11'GROUP BY DealerCode,iis.ForecastId,fs.ForecastId,NameAnd I come out with all the rows from the 1st table (Sales).[code]DealerCode ForecastId ForecastId Name0011 4 4 Dual0011 5 5 Mortgage0011 6 6 Retail[/code]But in the 2nd table have 3 more records (7, 8, and 9).Where are those???I tried this with Left and Right Joins as well as Full.Still didn't get the extra records.This should be pretty straight forward.Any ideas what I am missing???Thanks,Tom[/quote]Hi Tom,Can you please provide DDL and sample records of the tables ?Mon, 20 Aug 2012 21:18:40 GMTrhythmkhttp://www.sqlservercentral.com/Forums/Topic1347538-392-1.aspxI assume, DealerCode belongs to table [dbo].[Sales].[code="sql"]SELECT [DealerCode],iis.[ForecastSegmentId],fs.ForecastSegmentId,[Name]FROM [dbo].[Sales] iisRIGHT OUTER JOIN dbo.Forecast fs ON fs.ForecastId = iis.ForecastId and iis.dealercode = '11'GROUP BY iis.DealerCode,iis.ForecastId,fs.ForecastId,Name[/code]Mon, 20 Aug 2012 20:51:21 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1347538-392-1.aspxActually, the topic should say RIGHT JOIN and not LEFT JOIN.Mon, 20 Aug 2012 20:31:50 GMTtshadhttp://www.sqlservercentral.com/Forums/Topic1347538-392-1.aspxI have 2 tables in my database and am RIGHT JOINING the tables and it is acting like an inner joinSELECT [DealerCode] ,iis.[ForecastSegmentId] ,fs.ForecastSegmentId ,[Name] FROM [dbo].[Sales] iis RIGHT OUTER JOIN dbo.Forecast fs ON fs.ForecastId = iis.ForecastId WHERE dealercode = '11'GROUP BY DealerCode,iis.ForecastId,fs.ForecastId,NameAnd I come out with all the rows from the 1st table (Sales).[code]DealerCode ForecastId ForecastId Name0011 4 4 Dual0011 5 5 Mortgage0011 6 6 Retail[/code]But in the 2nd table have 3 more records (7, 8, and 9).Where are those???I tried this with Left and Right Joins as well as Full.Still didn't get the extra records.This should be pretty straight forward.Any ideas what I am missing???Thanks,TomMon, 20 Aug 2012 20:29:43 GMTtshad