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_Gene ral_CP1_CI_AS
NULL ,
[FranDealerCode] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[Line_No] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[Current] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[YTD] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[12Months] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[24Months] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[Average_YTD] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[Average12months] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[Average24Months] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[Last_YTD] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[Current_Status] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[PD1] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD2] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD3] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD4] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD5] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD6] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD7] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD8] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD9] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD10] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD11] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD12] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD13] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD14] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD15] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD16] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD17] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD18] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD19] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD20] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD21] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD22] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD23] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD24] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD25] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD26] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD27] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD28] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD29] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD30] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD31] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD32] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD33] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD34] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD35] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PD36] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[SortOrder] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [RDOData_Extract _Lines] (
[DeptId] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[DeptDesc] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[LineNum] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[LineDesc] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[SortOrder1] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[SortOrder2] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
) ON [PRIMARY]
GO
2  6385 
Ryan (ry********@hot mail.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****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Cool. Knew I'd missed something simple. Just having a bad day. Thanks !
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: StealthBananaT |
last post by:
My database has two tables - films has 10,000 records and reviews has 20,000 records. Whenever I try to list all the films and the
count of its reviews, MySQL locks and I have to restart the service.
Here's the SQL...
SELECT films.*, COUNT(reviews.id)
FROM films LEFT JOIN reviews ON films.id = reviews.filmID
GROUP BY films.id
LIMIT 0, 100;
|
by: Marek Lewczuk |
last post by:
Hello,
I have a strange problem, maybe some of you will be able to explain me
something. I use LEFT JOIN as a substitute for subselects. It's true
that many subselects can be rewriten using LEFT JOIN. I have made a
query which use LEFT JOIN statement and... when there are many LEFT
JOIN's (over 3) on the same table MySQL execute this query very long
time... few hours or more. Maybe there is something wrong with my table
structures... Are...
|
by: Marek Kotowski |
last post by:
In MySQL online documentation there are some examples with
multi-tables left joins. But all of them are like this (taken from the
documentation):
SELECT ...
FROM table1
LEFT JOIN table2 on (table1.id = table2.id)
LEFT JOIN table3 on (table1.id2 = table3.id2)
LEFT JOIN table4 on (table1.id3 = table4.id3)
|
by: Eric |
last post by:
Hi Folks,
Lets assume I have three tables. Their layout is as follows. Please
note that tblPeople does not have an entry for Denver (this is my
problem)
tblCity
_________________
CityName OCID
LA 1
Denver 2
|
by: Quarco |
last post by:
Hi,
Suppose I have a query like:
SELECT
products.name AS product,
SUM(IF(stock.invoice=0,1,0)) AS in_stock,
SUM(IF(shopcart.status=1,1,0)) AS reserved
FROM products
LEFT JOIN stock ON products.id=stock.product_id
| | |
by: Dam |
last post by:
Using SqlServer :
Query 1 :
SELECT def.lID as IdDefinition,
TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour
FROM serveur.Data_tblDEFINITIONTABLEDECODES def,
serveur.Data_tblTABLEDECODEAUNEVALEUR TDC_AUneValeur
where def.TYPEDETABLEDECODES = 4
|
by: Steve |
last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
"Left Outer Joins" in order to return every transaction for a specific
set of criteria.
Using three "Left Outer Joins" slows the system down considerably.
I've tried creating a temp db, but I can't figure out how to execute
two select commands. (It throws the exception "The column prefix
'tempdb' does not match with a table name or alias name used in the
query.")
|
by: deko |
last post by:
SELECT tblTxAcct.TxAcctName, tblTxType.TxTypeName,
Nz(qryTxAcctListCt.TxCount, 0) AS TxCt
FROM (tblTxAcct INNER JOIN tblTxType ON
tblTxAcct.TxType_ID=tblTxType.TxType_ID) LEFT JOIN qryTxAcctListCt ON
tblTxAcct.TxAcct_ID=qryTxAcctListCt.TxAcct_ID;
I use this query as a RecordSource for a Datasheet that displays Accounts,
Types, and the number of Transactions in each Account. An Account cannot
exist outside of a Type, but an Account...
|
by: Doug |
last post by:
Hi,
I'm more familiar with MSSQL than Access syntax and have run into a problem
with correctly putting ( )'s around the joins in a 3 table query. I want to
INNER JOIN lenders and accounts and LEFT OUTER JOIN that result with
prospects. (I want to receive all the results of the inner join and any
pertinent info from table 3 that is available.)
The way it was written in MSSQL was basically..
|
by: shapper |
last post by:
Hello,
I am used to SQL but I am starting to use LINQ.
How can I create Left, Right and Inner joins in LINQ? How to
distinguish the different joins?
Here is a great SQL example:
http://www.codinghorror.com/blog/archives/000976.html
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
