|
Home > Archive > Microsoft SQL Server forum > September 2005 > Left Join Problem
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| 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_Line
s 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_Line
s 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 & #91;RDOData_Extract]
(
[Dealer_Code] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS
NULL ,
[FranDealerCode] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS
NULL ,
[Line_No] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[Current] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[YTD] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[12Months] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[24Months] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[Average_YTD] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS
NULL ,
& #91;Average12months]
[varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS
NULL ,
& #91;Average24Months]
[varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS
NULL ,
[Last_YTD] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[Current_Status] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS
NULL ,
[PD1] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD2] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD3] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD4] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD5] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD6] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD7] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD8] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD9] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD10] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD11] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD12] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD13] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD14] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD15] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD16] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD17] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD18] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD19] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD20] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD21] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD22] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD23] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD24] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD25] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD26] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD27] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD28] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD29] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD30] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD31] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD32] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD33] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD34] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD35] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[PD36] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[SortOrder] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE & #91;RDOData_Extract_
Lines] (
[DeptId] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[DeptDesc] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[LineNum] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[LineDesc] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[SortOrder1] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL
,
[SortOrder2] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL
) ON [PRIMARY]
GO
| |
| Erland Sommarskog 2005-09-02, 7:23 am |
| Ryan (ryanofford@hotmail.com) writes:
> SELECT
> L.LineDesc,
> D.*
>
> FROM
> RDOData_Extract_Line
s 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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
| |
|
| Cool. Knew I'd missed something simple. Just having a bad day. Thanks !
|
|
|
|
|