August 23, 2012 at 11:43 am
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
August 24, 2012 at 2:18 am
tshad (8/23/2012)
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
Can you post it here Tom with DDL and perhaps some sample data? It's time we put this one to bed 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 31, 2012 at 3:16 pm
I 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,
Tom
September 5, 2012 at 11:40 am
I 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 records
2) Left Join the table you want the records to go into
3) Filter out the non nulls
SELECT *
FROM (SELECT *
FROM DealerForecast f
CROSS JOIN Models m) mdf
LEFT OUTER JOIN DealerForecastModel dfm
ON mdf.DealerForecastId = dfm.DealerForecastId AND mdf.ModelID = dfm.ModelID
WHERE DealerForecastModelID IS NULL
The Schema I used was:
ALTER PROC [dbo].[CreateSchema]
AS
IF (EXISTS ( SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'DealerForecastModel'))
DROP Table DealerForecastModel
IF (EXISTS ( SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'DealerForecast'))
DROP Table DealerForecast
IF (EXISTS ( SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'ModelForecast'))
DROP Table ModelForecast
IF (EXISTS ( SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'Dealers'))
DROP Table Dealers
IF (EXISTS ( SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'Models'))
DROP Table Models
IF (EXISTS ( SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'Forecast'))
DROP Table Forecast
CREATE 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)
Thanks for all the help,
Tom
September 6, 2012 at 2:27 am
Looks fine to me, Tom. Thanks for the feedback.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 6, 2012 at 3:18 pm
Hi 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 f
CROSS JOIN Models m) mdf
LEFT OUTER JOIN DealerForecastModel dfm
ON mdf.DealerForecastId = dfm.DealerForecastId AND mdf.ModelID = dfm.ModelID
WHERE DealerForecastModelID IS NULL and IsActive = 1
But 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 f
CROSS JOIN (select * from Models where IsActive = 1) m) mdf
LEFT OUTER JOIN DealerForecastModel dfm
ON mdf.DealerForecastId = dfm.DealerForecastId AND mdf.ModelID = dfm.ModelID
WHERE DealerForecastModelID IS NULL
Is that the best way?
Thanks,
Tom
September 7, 2012 at 2:38 am
Looks 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:
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
) mdf
LEFT OUTER JOIN DealerForecastModel dfm
ON mdf.DealerForecastId = dfm.DealerForecastId
AND mdf.ModelID = dfm.ModelID
WHERE dfm.DealerForecastModelID IS NULL
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 14, 2012 at 10:24 pm
Looks good.
Thanks,
Tom
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply