Subquery Issues - HELP!!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Subquery Issues - HELP!!!

Author

Topic

CamposSQL
Starting Member

4 Posts

Posted - 2006-05-15 : 14:23:22

Ok - I'm trying to get all the DealerRegions table records that are NOT in the CourseComplete table which, if it did exist, would have the same Dealer Code. There may be several records in the CourseComplete table with the same dealer code. Here's what I have...


SELECT DISTINCT DEALER_CODE, DEALER_NAME, REGION
FROM         DealerRegions
WHERE     (DEALER_CODE <> ANY
                          (SELECT     dealerCode
                            FROM          CourseComplete)) AND (REGION = N'WE')

The query returns all of the Dealers in the 'WE' region without filtering out those in the CourseComplete table. I don't get it!

TIA,
CamposSQL

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-15 : 14:26:17

Could u provide some sample data and desired results

Srinika

CamposSQL
Starting Member

4 Posts

Posted - 2006-05-15 : 14:33:41

DealerRegions table:
DEALER_CODE DEALER_NAME REGION
----------------------------------
AB02 Jim Honda WE
AC03 Jane Honda WE
AD04 Jack Honda WE
AE05 John Honda CE

CourseComplete table:
techName dealerCode grade
------------------------------
Phil S AB02 78.3
Greg R AC03 65.2
Frank H AB02 91.8
Steve C AE05 100.0

I'm trying to get the return value of all Regions not represented in the CourseComplete table. I would like:

AD04 Jack Honda WE

Thanx,
CamposSQL

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-15 : 15:01:07

[code]Create Table #DealerRegions (DEALER_CODE varchar(10), DEALER_NAME varchar(50), REGION varchar(10))
Insert into #DealerRegions values('AB02', 'Jim Honda', 'WE')
Insert into #DealerRegions values('AC03', 'Jane Honda', 'WE')
Insert into #DealerRegions values('AD04', 'Jack Honda', 'WE')
Insert into #DealerRegions values('AE05', 'John Honda', 'CE')

Create Table #CourseComplete (techName varchar(50), DEALER_CODE varchar(10), grade decimal)
Insert into #CourseComplete values('Phil S', 'AB02', 78.3)
Insert into #CourseComplete values('Greg R', 'AC03', 65.2)
Insert into #CourseComplete values('Frank H', 'AB02', 91.8)
Insert into #CourseComplete values('Steve C', 'AE05', 100.0)

Select * from #DealerRegions
where DEALER_CODE not in (Select DEALER_CODE from #CourseComplete )

Drop Table #DealerRegions
Drop Table #CourseComplete [/code]

Srinika

CamposSQL
Starting Member

4 Posts

Posted - 2006-05-15 : 15:14:49

Nope... Still didn't work. If i remove 'NOT' I'll get what i expect, 3 records, but when i include the 'NOT' it returns 'NULL' records. By the way, i'm using MSSQL 2000 and test the queries with VS 2005 Pro Query Designer.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-15 : 15:29:36

quote:
Originally posted by CamposSQL

... I'll get what i expect, 3 records ....

I ur earlier post ur expectation was to have only 1 rec

Copy & paste the code I gave u and c the results. It will return exactly u asked in the earlier post. It is not returning Null records for the data u provided.
i tested it.

Srinika

CamposSQL
Starting Member

4 Posts

Posted - 2006-05-15 : 16:00:02

I apologize - your code did work and return the desired results. I looked over the CourseComplete table and noticed some 'NULL' values in the DealerCode column. When i removed them the query worked. The pain of working with someone else's tables. THANKS SO MUCH.

CamposSQL

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-16 : 07:15:05

Yeah, this is the cause of many a headache!

When 'not in' is used, it's often a good idea to combine that with an 'is not null'. e.g.

Select * from #DealerRegions
where DEALER_CODE not in (Select DEALER_CODE from #CourseComplete where DEALER_CODE is not null)

Or to use 'exists' or a join instead.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-05-16 : 07:42:31

If you use exists operator, you don't worry about nulls:


SELECT DISTINCT DEALER_CODE, DEALER_NAME, REGION
FROM         DealerRegions reg
WHERE    not exists (SELECT *
                            FROM          CourseComplete cc
                            WHERE cc.dealerCode = reg.dealer_code)
 AND (REGION = N'WE')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources