Cannot get Left JOIN to work correctly

  • 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

  • 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 😉

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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

  • 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

  • Looks fine to me, Tom. Thanks for the feedback.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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

  • 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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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