Select statement based upon relationship of two different fields 1

[Haybails90]

Hey there folks,

I'm having trouble wrapping my brain around this one.

I've inherited a database from a previous (no longer employeed here - LOL) developer which has data spread across multiple tables and is not very normalized. We'll be doing a database restructure to cure this problem, but for now I need some help constructing a query.

Here's the scenario: We have two tables 'contractor' and 'contractor_sales'. EACH table has a 'store_no' field and EACH table has a 'contractor_no' field. Now, it seems we have some orphaned records in the 'contractor_sales' table and no related records in the 'contractor' table. So, what I need to do is return a listing of all records from 'contractor_sales' which do not have a related record in 'contractor'.

I've tried the following SQL statement in Query Analyzer, but it's not doing what I need. Here's what I tried:

SELECT * FROM contractor_sales
WHERE
store_no NOT IN (SELECT DISTINCT store_no FROM contractor)
AND
contractor_no NOT IN (SELECT DISTINCT contractor_no FROM contractor)
ORDER BY store_no, contractor_no

The key is matching up BOTH the store_no and the contractor_no as we may have records in the 'contractor' table which have the the right store_no value I'm looking for, but a different contractor_no value. Make sense? Like for example:
store_no of 395 and contractor_no of 123456
store_no of 395 and contractor_no of 654321

Any assistance would be greatly appreciated.



Haybails90

 

[dbomrrsm]

Try

SELECT * FROM contractor_sales
where not exists
(select * from contractor
WHERE store_no = contractor_sales.store_no
and contractor_no = contractor_sales.contractor_no)

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Haybails90]

DBomrrsm,

At first glance, that appears to be the ticket. It appears that I was close ... or, at least, generally headed in the right direction with my attempt. But, again, thank you for your assistance.



Haybails90

 

[dbomrrsm]

Your welcome

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]