Finding unmatched records - 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

Transact-SQL (2000)

Finding unmatched records

Author

Topic

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-03-05 : 06:25:54

Hi I have two tables whereby I want to find those records in one table that don't exist in the other. My query is as follows:

SELECT "CUSTOMER NUMBER"
FROM NON_TRADING_BEAUTY_SALONS
WHERE "CUSTOMER NUMBER" NOT IN
(SELECT "CUSTOMER NUMBER"
FROM EVERY_TANNING_SALON)

This doesn't return anything, but I know that some records don't match. If I change the "NOT IN" to "IN" the query still doesn't return anything.
What am I doing wrong???

Cheers

Paul

Robwhittaker
Yak Posting Veteran

85 Posts

Posted - 2003-03-05 : 06:34:15

Paul,

Try losing the quotes. Anything in quotes will be seen as a literal not the contents of a column.

If that doesn't work, make sure your datatypes are the same, and look for problems with trailing and leading blanks etc.

Rob

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-03-05 : 06:35:42

Hi Rob,

I can't lose the quotes as ther is a space in the column name. If i remove the quotes it doesn't understand the column I'm trying to reference.

Cheers

Paul

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-03-05 : 06:41:36

Hi Rob,

I've just renamed the column name to customer_number so that I can remove the quotes. I've checked the datatypes and they are the same, there are also no trailing or leading spaces, but the query still doesn't return any results.

Any other ideas??

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2003-03-05 : 06:58:12

Try
select Customer_Number
from NON_TRADING_BEAUTY_SALONS
where Customer_Number not in
(SELECT Customer_Number from EVERY_TANNING_SALON)

Or:
select N.Customer_Number
FROM NON_TRADING_BEAUTY_SALONS N FULL OUTER JOIN EVERY_TANNING_SALON E
ON N.Customer_Number = E.Customer_Number
WHERE E.Customer_Number IS NULL

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-03-05 : 07:10:51

Hi Yellow Bug,

Your first suggestion still doesn't work, but the second query does. Could you explain how this second query works as I am finding it hard to follow.

Cheers

Paul

Robwhittaker
Yak Posting Veteran

85 Posts

Posted - 2003-03-05 : 07:25:09

Paul,

in terms of column names, spaces are bad, try and avoid them. If you do have spaces, or column names that are no nos like "table", "column"
etc, you need to put them in [] not "s

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-03-05 : 08:20:35

quote:

you need to put them in [] not "s

and you need to read the ANSI standard

My guess is that there's a NULL in EVERY_TANNING_SALON."CUSTOMER NUMBER"
See this thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23375

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-03-05 : 09:17:51

Hi Arnold,

You hit the nail on the head!!! I had 1 record with a null value in the customer number. The link you provided also helped me understand why the null value produced such a result.

Thanks Alot

Paul

precept
Starting Member

5 Posts

Posted - 2003-03-05 : 17:46:51

You should be able to make a left outer join with NON_TRADING_BEAUTY_SALONS left outer join EVERY_TANNING_SALON
where the customer number in the EVERY_TANNING_SALON is null

select n.customer_number
from NON_TRADING_BEAUTY_SALONS n
left outer join EVERY_TANNING_SALON e
on n.customer_number = e.customer_number
where e.customer_number is null

this will return all cust numbers found in NON_TRADING_BEAUTY_SALONS that don't have a match in EVERY_TANNING_SALON...left outer will grab all records to the left then discriminate based on nulls

Edited by - precept on 03/05/2003 17:48:08

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources