Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cannot get Left JOIN to work correctly


Cannot get Left JOIN to work correctly

Author
Message
tshad
tshad
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 454
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
tshad
tshad
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 454
Actually, the topic should say RIGHT JOIN and not LEFT JOIN.
Sergiy
Sergiy
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6436 Visits: 11547
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


rhythmk
rhythmk
Right there with Babe
Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)

Group: General Forum Members
Points: 720 Visits: 1063
tshad (8/20/2012)
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



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/
:-)
tshad
tshad
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 454
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
Lynn Pettis
Lynn Pettis
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26668 Visits: 38137
tshad (8/20/2012)
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


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)
rhythmk
rhythmk
Right there with Babe
Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)

Group: General Forum Members
Points: 720 Visits: 1063
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/
:-)
tshad
tshad
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 454
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
Cadavre
Cadavre
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2870 Visits: 8447
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.


Forever 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


Craig Wilkinson - Software Engineer
LinkedIn
ChrisM@Work
ChrisM@Work
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10293 Visits: 19231
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search