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 1 of 3

1 2 3 »»»

Cannot get Left JOIN to work correctly

Rate Topic

Display Mode

Topic Options

Author

Message

tshad

Posted Monday, August 20, 2012 8:29 PM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 2:12 AM
Points: 99, Visits: 262

I have 2 tables in my database and am RIGHT JOINING the tables and it is acting like an inner join

SELECT [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,Name

And I come out with all the rows from the 1st table (Sales).

DealerCode	ForecastId	ForecastId	Name
0011	4	4	Dual
0011	5	5	Mortgage
0011	6	6	Retail

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

Post #1347538

tshad

Posted Monday, August 20, 2012 8:31 PM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 2:12 AM
Points: 99, Visits: 262

Actually, the topic should say RIGHT JOIN and not LEFT JOIN.

Post #1347540

Sergiy

Posted Monday, August 20, 2012 8:51 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:01 PM
Points: 4,540, Visits: 8,184

I assume, DealerCode belongs to table [dbo].[Sales].

SELECT [DealerCode]
,iis.[ForecastSegmentId]
,fs.ForecastSegmentId
,[Name]
FROM [dbo].[Sales] iis
RIGHT OUTER JOIN dbo.Forecast fs ON fs.ForecastId = iis.ForecastId and iis.dealercode = '11'
GROUP BY iis.DealerCode,iis.ForecastId,fs.ForecastId,Name

Post #1347541

rhythmk

Posted Monday, August 20, 2012 9:18 PM

Old Hand

Group: General Forum Members
Last Login: Saturday, November 10, 2012 9:30 AM
Points: 388, Visits: 603

tshad (8/20/2012)

DealerCode	ForecastId	ForecastId	Name
0011	4	4	Dual
0011	5	5	Mortgage
0011	6	6	Retail

Hi Tom,

Can you please provide DDL and sample records of the tables ?

--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Smile

Post #1347544

tshad

Posted Monday, August 20, 2012 9:37 PM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 2:12 AM
Points: 99, Visits: 262

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

Post #1347547

Lynn Pettis

Posted Monday, August 20, 2012 9:59 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:34 AM
Points: 21,588, Visits: 27,385

tshad (8/20/2012)

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.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1347553

rhythmk

Posted Monday, August 20, 2012 10:10 PM

Old Hand

Group: General Forum Members
Last Login: Saturday, November 10, 2012 9:30 AM
Points: 388, Visits: 603

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

--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Smile

Post #1347556

tshad

Posted Tuesday, August 21, 2012 1:06 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 2:12 AM
Points: 99, Visits: 262

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:

DealerCode	ForecastId	ForecastId	Name
NULL	NULL	7	Wholesale
NULL	NULL	8	Region
NULL	NULL	9	Market
11	4	4	Dual
11	5	5	Mortgage
11	6	6	Retail

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:

DealerCode	ForecastId	ForecastId	Name
NULL	NULL	9	Market
11	4	4	Dual
11	5	5	Mortgage
11	6	6	Retail
5	4	4	Dual
5	5	5	Mortgage
5	6	6	Retail
5	7	7	Wholesale
5	8	8	Region
8	4	4	Dual

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:

DealerCode	ForecastId	ForecastId	Name
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

Thanks,

Tom

Post #1347595

Cadavre

Posted Tuesday, August 21, 2012 2:40 AM

SSCrazy

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

The closest I can come is this: -

SELECT s.DealerCode, s.ForecastID, fc.ForecastId, fc.Name
FROM [dbo].[Forecast] fc
OUTER APPLY (SELECT code
             FROM (VALUES('5'),('8'),('11'))a(code)
             ) c
LEFT OUTER JOIN [dbo].[Sales] s ON s.DealerCode = c.code AND s.ForecastID = fc.ForecastID
ORDER BY fc.Name;

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): -

SET NOCOUNT ON;

--Create tables
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 sample data
INSERT INTO [dbo].[Sales]
SELECT SalesID, DealerCode, ForecastID
FROM (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, Name
FROM (VALUES(4, 'Dual'),(5, 'Mortgage'),(6, 'Retail'),(7, 'Wholesale'),
            (8, 'Region'),(9, 'Market')
      )a(ForecastID, Name);
            
--Expected result
SELECT DealerCode, SalesForecastId, ForecastForecastId, Name
FROM (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;

Here's my attempt: -

--Actual Query
SELECT s.DealerCode, s.ForecastID, fc.ForecastId, fc.Name
FROM [dbo].[Forecast] fc
OUTER APPLY (SELECT code
             FROM (VALUES('5'),('8'),('11'))a(code)
             ) c
LEFT OUTER JOIN [dbo].[Sales] s ON s.DealerCode = c.code AND s.ForecastID = fc.ForecastID
ORDER BY fc.Name;

Results in: -

DealerCode      ForecastID  ForecastId  Name
--------------- ----------- ----------- --------------------------------------------------
5               4           4           Dual
8               4           4           Dual
11              4           4           Dual
NULL            NULL        9           Market
NULL            NULL        9           Market
NULL            NULL        9           Market
5               5           5           Mortgage
NULL            NULL        5           Mortgage
11              5           5           Mortgage
5               8           8           Region
NULL            NULL        8           Region
NULL            NULL        8           Region
5               6           6           Retail
NULL            NULL        6           Retail
11              6           6           Retail
5               7           7           Wholesale
NULL            NULL        7           Wholesale
NULL            NULL        7           Wholesale

But you have your expected result as : -

DealerCode  SalesForecastId ForecastForecastId Name
----------- --------------- ------------------ ---------
5           4               4                  Dual
11          4               4                  Dual
8           4               4                  Dual
NULL        NULL            9                  Market
NULL        NULL            9                  Market
NULL        NULL            9                  Market
5           5               5                  Mortgage
11          5               5                  Mortgage
NULL        NULL            8                  Region
NULL        NULL            8                  Region
5           8               8                  Region
NULL        NULL            8                  Region
5           6               6                  Retail
11          6               6                  Retail
NULL        NULL            7                  Wholesale
5           7               7                  Wholesale
NULL        NULL            7                  Wholesale

If you can explain the expected result, I'm sure that someone will be able to help.

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 #1347635

ChrisM@Work

Posted Tuesday, August 21, 2012 5:58 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:21 AM
Points: 5,602, Visits: 10,950

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

^{“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 #1347732

« Prev Topic | Next Topic »

23 posts, Page 1 of 3

1 2 3 »»»

Permissions