non-matching recrods 2 tables (Newbie)

Hi,

I am new to SQL and these forums, and have a quick simple question.

I am trying to view the records that do not match in 2 tables. I have tried a few different ways but keep getting results in the hundreds of thousands and the table only has 36 thousand reocrds.

Here is an example of what the two tables contain.

Table 1
customer_no eaddress_no name address (36000 records)

Table2
customer_no eaddress_no email (17000 records)

I need to find out which customers are not in table 2 by linking the eaddress_no numbers.

This is one of the scripts i ran that gives me the large results with a ton of dups:

SELECT *
FROM T_EADDRESS inner JOIN
T_CUST_LOGIN ON T_EADDRESS.eaddress_no
!= T_CUST_LOGIN.eaddress_no

Thanks in advance for any help with this.Try this:

SELECT Table1.*
FROM Table1 INNER JOIN Table2
ON Table1.eaddress_no <> Table2.eaddress_no|||I got the same results:

Alot of dups.

I did just find this at SQLTeam after i posted this message. I am just getting ready to try it now.

SELECT A.No,A.Date
FROM A LEFT OUTER JOIN B ON A.No = B.No AND A.Date = B.Date WHERE B.No IS NOT NULL|||If your query does not work, try this:

SELECT *
FROM Table1
WHERE eaddress_no NOT IN
(SELECT eaddress_no FROM Table2)|||gyuan that worked.
yours looked a little easier for me to follow so i just used that one.
Thanks a million.|||Using a left join is generally preferable to NOT EXISTS.

SELECT Table1.*
FROM Table1
left outer join Table2 on Table1.eaddress_no = Table2.eaddress_no)
WHERE Table1.eaddress_no is null

This can also be easily be modified to show records that are missing from either table:

SELECT Table1.*, Table2.*
FROM Table1 full outer join Table2 on Table1.eaddress_no = Table2.eaddress_no)
WHERE Table1.eaddress_no is null or Table2.eaddress_no is null|||Thanks Blind Man,

Those make sense to me now . I helps to see the table names in the query.|||Blindman,

you have an extra ) where would the opening one go?|||Just remove it...you don't need it...|||Sorry. No Parenthesis is necessary. It was a copy/paste error.|||I tried that and it returns no records.|||If you run this

SELECT Table1.*
FROM Table1
left outer join Table2 on Table1.eaddress_no = Table2.eaddress_no
WHERE Table1.eaddress_no is null

and no records are returned, then there are no eaddress_no values in Table1 that are not also in Table2.

Run the second one with the FULL OUTER JOIN clause and see what you get:

SELECT Table1.*, Table2.*
FROM Table1 full outer join Table2 on Table1.eaddress_no = Table2.eaddress_no
WHERE Table1.eaddress_no is null or Table2.eaddress_no is null|||OOPS!
WHERE clause was looking at wrong table! We want to see if Table2 is null...

SELECT Table1.*
FROM Table1
left outer join Table2 on Table1.eaddress_no = Table2.eaddress_no
WHERE Table2.eaddress_no is null|||Yep that did it. And it took about 2 seconds few to run.

Thanks blindman, now i see.