Help with NOT EXISTS query

Newbie here...looking for help with a NOT EXISTS query or suggestions for a
better method.
I have 2 linked tables: CUSTOMERS and ADDRESSES
common fields are CUS_NO and ADR_CD
I need to find records where an address code (ADR_CD) entered into CUSTOMERS
does not have that same ADR_CD existing in the ADRESSES table.

Example:
CUS_NO = 12345
ADR_CD = Ohio01

If the combination of cus_no 12345 and Ohio01 does not exist in the
ADDRESSES table, I need to find them.

Thanks in advance.RDRaider wrote:
> Newbie here...looking for help with a NOT EXISTS query or suggestions for a
> better method.
> I have 2 linked tables: CUSTOMERS and ADDRESSES
> common fields are CUS_NO and ADR_CD
> I need to find records where an address code (ADR_CD) entered into CUSTOMERS
> does not have that same ADR_CD existing in the ADRESSES table.
> Example:
> CUS_NO = 12345
> ADR_CD = Ohio01
> If the combination of cus_no 12345 and Ohio01 does not exist in the
> ADDRESSES table, I need to find them.
> Thanks in advance.

SELECT <select list>
FROM Customers c
WHERE NOT EXISTS (SELECT *
FROM Addresses a
WHERE c.Cus_No = a.Cust_No
AND c.ADR_CD = a.ADR_CD)

Zach|||On Mon, 13 Dec 2004 20:17:45 GMT, RDRaider wrote:

> Newbie here...looking for help with a NOT EXISTS query or suggestions for a
> better method.
> I have 2 linked tables: CUSTOMERS and ADDRESSES
> common fields are CUS_NO and ADR_CD
> I need to find records where an address code (ADR_CD) entered into CUSTOMERS
> does not have that same ADR_CD existing in the ADRESSES table.
> Example:
> CUS_NO = 12345
> ADR_CD = Ohio01
> If the combination of cus_no 12345 and Ohio01 does not exist in the
> ADDRESSES table, I need to find them.
> Thanks in advance.

SELECT Customers.cus_no, Customers.adr_cd
FROM Customers
WHERE NOT EXISTS
(SELECT *
FROM ADDRESSES
WHERE Customers.cus_no = ADDRESSES.cus_no
AND Customers.adr_cd = Addresses.adr_cd )

Alternative method:

SELECT Customers.cus_no, Customers.adr_cd
FROM Customers
LEFT JOIN Addresses
ON Customers.cus_no = Addresses.cus_no
AND Customers.adr_cd = Addresses.adr_cd
WHERE Addresses.cus_no IS NULL|||Thanks for the quick reply. It works! I don't know why I couldn't get the
same results, need to hit the books I guess.

"nib" <individual_news@.nibsworld.com> wrote in message
news:326chiF3i7ns1U1@.individual.net...
> RDRaider wrote:
>> Newbie here...looking for help with a NOT EXISTS query or suggestions for
>> a better method.
>> I have 2 linked tables: CUSTOMERS and ADDRESSES
>> common fields are CUS_NO and ADR_CD
>> I need to find records where an address code (ADR_CD) entered into
>> CUSTOMERS does not have that same ADR_CD existing in the ADRESSES table.
>>
>> Example:
>> CUS_NO = 12345
>> ADR_CD = Ohio01
>>
>> If the combination of cus_no 12345 and Ohio01 does not exist in the
>> ADDRESSES table, I need to find them.
>>
>> Thanks in advance.
> SELECT <select list>
> FROM Customers c
> WHERE NOT EXISTS (SELECT *
> FROM Addresses a
> WHERE c.Cus_No = a.Cust_No
> AND c.ADR_CD = a.ADR_CD)
> Zach