Left Join Problem

[Ryan]

I'm going daft. I have what should be a simple query and it seems that
the left side of the join is being ignored. The query and DDL are
below. Basically, my RDOData_Extract_Lines table (where LineNum NOT
LIKE 'LAN%') on it's own gives me 959 records. If I look at the
RDOData_Extract table (with the dealer code = 8494) it shows 521
records. Using the query below and specifically a left join it 'should'
show me 959 records, but only show data for 521 of them. However, it
shows me 521 records. What have I done wrong ?

Thanks

Ryan

SELECT
L.LineDesc,
D.*

FROM
RDOData_Extract_Lines L
LEFT JOIN RDOData_Extract D
ON L.LineNum = D.Line_No

WHERE
L.LineNum NOT LIKE 'LAN%' AND
D.Dealer_Code = 8494

CREATE TABLE [RDOData_Extract] (
[Dealer_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[FranDealerCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Line_No] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Current] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[YTD] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[12Months] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[24Months] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Average_YTD] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Average12months] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Average24Months] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Last_YTD] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Current_Status] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PD1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD4] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD5] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD6] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD7] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD8] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD9] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD10] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD11] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD12] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD13] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD14] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD15] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD16] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD17] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD18] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD19] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD20] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD21] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD22] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD23] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD24] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD25] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD26] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD27] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD28] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD29] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD30] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD31] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD32] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD33] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD34] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD35] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD36] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SortOrder] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [RDOData_Extract_Lines] (
[DeptId] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeptDesc] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LineNum] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LineDesc] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SortOrder1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[SortOrder2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]
GO

[Erland Sommarskog]

Ryan (ry********@hotmail.com) writes:

SELECT
L.LineDesc,
D.*

FROM
RDOData_Extract_Lines L
LEFT JOIN RDOData_Extract D
ON L.LineNum = D.Line_No

WHERE
L.LineNum NOT LIKE 'LAN%' AND
D.Dealer_Code = 8494

This how it works: first you have a table specified with FROM. Then you
get a new table (conceptually) by with JOIN. When you use a LEFT JOIN
all rows in the left tables are included, and the columns from the
right table as NULL.

Then you apply a WHERE clause on this table and filter rows with the
conditions you have. With the condition on D.Dealer_code, all rows
with NULL goes out the window.

This gives a coupld of ways to fix this. The most common and probably the
best is to move the condition on Dealer_code to the ON part.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ryan]

Cool. Knew I'd missed something simple. Just having a bad day. Thanks !