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
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, esq...@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp