Cannot get Left JOIN to work correctly

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Cannot get Left JOIN to work correctly

23 posts, Page 2 of 3

««1 2 3 »»

Cannot get Left JOIN to work correctly

Rate Topic

Display Mode

Topic Options

Author

Message

tshad

Posted Thursday, August 23, 2012 12:22 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:12 AM
Points: 99, Visits: 262

That worked really good.

But I couldn't get it to work with a many to many table in the middle

Here 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, Name
FROM Dealers d
JOIN DealerForecastMap dfm
ON d.DealerID = dfm.DealerID
JOIN Forecast f
ON f.ForecastId = dfm.ForecastID

And the results:

DealerID	DealerCode	DealerName	ForecastID	Name
1	11	Sears	4	Dual
1	11	Sears	5	Mortgage
1	11	Sears	6	Retail
2	05	Pick And Save	4	Dual
2	05	Pick And Save	5	Mortgage
2	05	Pick And Save	6	Retail
2	05	Pick And Save	7	Wholesale
2	05	Pick And Save	8	Region
3	08	Pennys	4	Dual

How would I show the results with each dealer showing all the Forecasts including the forecasts each one is missing?

Thanks,

Tom

Post #1348876

Cadavre

Posted Thursday, August 23, 2012 1:29 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236, Visits: 6,486

The same way we've already explained.

SELECT *
FROM (SELECT DealerID, DealerCode, DealerName, ForecastId, Name
      FROM Dealers d
      CROSS JOIN Forecast f) df
LEFT OUTER JOIN DealerForecastMap m ON df.ForecastId = m.ForecastId AND df.DealerID = m.DealerID;

Results in: -

DealerID    DealerCode      DealerName                                         ForecastId  Name                                               DealerForecastMapID DealerID        ForecastId
----------- --------------- -------------------------------------------------- ----------- -------------------------------------------------- ------------------- --------------- -----------
1           11              Sears                                              4           Dual                                               1                   1               4
1           11              Sears                                              5           Mortgage                                           2                   1               5
1           11              Sears                                              6           Retail                                             3                   1               6
1           11              Sears                                              7           Wholesale                                          NULL                NULL            NULL
1           11              Sears                                              8           Region                                             NULL                NULL            NULL
1           11              Sears                                              9           Market                                             NULL                NULL            NULL
2           05              Pick And Save                                      4           Dual                                               4                   2               4
2           05              Pick And Save                                      5           Mortgage                                           5                   2               5
2           05              Pick And Save                                      6           Retail                                             6                   2               6
2           05              Pick And Save                                      7           Wholesale                                          7                   2               7
2           05              Pick And Save                                      8           Region                                             8                   2               8
2           05              Pick And Save                                      9           Market                                             NULL                NULL            NULL
3           08              Pennys                                             4           Dual                                               9                   3               4
3           08              Pennys                                             5           Mortgage                                           NULL                NULL            NULL
3           08              Pennys                                             6           Retail                                             NULL                NULL            NULL
3           08              Pennys                                             7           Wholesale                                          NULL                NULL            NULL
3           08              Pennys                                             8           Region                                             NULL                NULL            NULL
3           08              Pennys                                             9           Market                                             NULL                NULL            NULL

Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...
(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock

LinkedIn | Blog coming soon (for sufficiently large values of "soon" Wink

Post #1348895

tshad

Posted Thursday, August 23, 2012 2:01 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:12 AM
Points: 99, Visits: 262

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) df
LEFT 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) m
CROSS JOIN Forecast fc
LEFT JOIN [Sales] s
on s.DealerCode = m.DealerCode AND s.ForecastID = fc.ForecastId
ORDER BY m.DealerCode

I 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

Post #1348913

ChrisM@Work

Posted Thursday, August 23, 2012 2:18 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 5,618, Visits: 10,990

tshad (8/23/2012)

Hi Tom

In 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.

^{“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
Exploring Recursive CTEs by Example Dwain Camps

Post #1348923

Cadavre

Posted Thursday, August 23, 2012 2:40 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236, Visits: 6,486

tshad (8/23/2012)

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.

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.

LinkedIn | Blog coming soon (for sufficiently large values of "soon" Wink

Post #1348943

tshad

Posted Thursday, August 23, 2012 11:43 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:12 AM
Points: 99, Visits: 262

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

Post #1349275

ChrisM@Work

Posted Friday, August 24, 2012 2:18 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 5,618, Visits: 10,990

tshad (8/23/2012)

Can you post it here Tom with DDL and perhaps some sample data? It's time we put this one to bed Wink

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

Post #1349520

tshad

Posted Friday, August 31, 2012 3:16 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:12 AM
Points: 99, Visits: 262

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

Post #1353101

tshad

Posted Wednesday, September 05, 2012 11:40 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:12 AM
Points: 99, Visits: 262

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

Post #1354785

ChrisM@Work

Posted Thursday, September 06, 2012 2:27 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 5,618, Visits: 10,990

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}

Post #1355110

« Prev Topic | Next Topic »

23 posts, Page 2 of 3

««1 2 3 »»

Permissions