Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Cannot get Left JOIN to work correctly Expand / Collapse
Author
Message
Posted Monday, August 20, 2012 8:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:46 AM
Points: 147, Visits: 361
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
Posted Monday, August 20, 2012 8:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:46 AM
Points: 147, Visits: 361
Actually, the topic should say RIGHT JOIN and not LEFT JOIN.
Post #1347540
Posted Monday, August 20, 2012 8:51 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, October 13, 2014 8:08 PM
Points: 4,573, Visits: 8,353
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
Posted Monday, August 20, 2012 9:18 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, October 6, 2014 6:14 AM
Points: 564, Visits: 888
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/
Post #1347544
Posted Monday, August 20, 2012 9:37 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:46 AM
Points: 147, Visits: 361
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
Posted Monday, August 20, 2012 9:59 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 3:27 PM
Points: 20,734, Visits: 32,505
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.



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
Posted Monday, August 20, 2012 10:10 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, October 6, 2014 6:14 AM
Points: 564, Visits: 888
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/
Post #1347556
Posted Tuesday, August 21, 2012 1:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:46 AM
Points: 147, Visits: 361
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
Posted Tuesday, August 21, 2012 2:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:36 AM
Points: 2,379, Visits: 7,584
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" )!
Post #1347635
Posted Tuesday, August 21, 2012 5:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:44 AM
Points: 6,792, Visits: 14,005
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 »

Add to briefcase 123»»»

Permissions Expand / Collapse