How to find Unique records?

[fong.yang]

I have tried to use the query wizard to find unmatched records but it
doesn't seem to be going right. The results are still giving me some
records that are in both tables. I have two tables OriginalShops and
AddedShops. Both tables have the same information in them. I want to
find only records that are not contained it either tables. The
following is the SQL statement created from the wizard:

SELECT AddedShops.shop _name, AddedShops.addr ess_1, AddedShops.city ,
AddedShops.stat e, AddedShops.zip_ code, AddedShops.tele phone
FROM AddedShops LEFT JOIN OriginalShops ON AddedShops.tele phone =
OriginalShops.[PHONE NUMBER]
WHERE (((OriginalShop s.[PHONE NUMBER]) Is Null));

As I mentioned it doesn't look like it's finding umatched records. The
some of the records that it's returning are in both tables. Any help
would be appreciated. Thanks.

[Rick Wannall]

You're doing a LEFT JOIN between a null field in one table and the same
field in another table. The result of such an operation is probably
undefined. Not sure. At any rate, you can't really join two tables on a
field that is null.

You seem to be looking only for original shops with no phone number, and
then trying to match that (empty) phone number in the added shops list.

Did you perhaps mean to do the join on the shop name:

SELECT AddedShops.shop _name, AddedShops.addr ess_1, AddedShops.city ,
AddedShops.stat e, AddedShops.zip_ code, AddedShops.tele phone
FROM AddedShops LEFT JOIN OriginalShops ON AddedShops.shop _name =
OriginalShops.s hop_name
WHERE (((OriginalShop s.shop_name) Is Null));

Now THAT SQL would bring you back a list of shops in the added shops list
for which there is no match in the original shops list, based solely on
shop_name. Of course, if the shop_name is typed even slightly differently
in the two lists, the added shop will not be matched, even though (from a
user viewpoint) that shop is in the original list.

[fong.yang]

Is there a way to used a wild card like an * say there's "john's
autobody service" listed in original shops can i run the same query
using "john's autobody * " against added shops?
Thanks.
Rick Wannall wrote:

You're doing a LEFT JOIN between a null field in one table and the same
field in another table. The result of such an operation is probably
undefined. Not sure. At any rate, you can't really join two tables on a
field that is null.

You seem to be looking only for original shops with no phone number, and
then trying to match that (empty) phone number in the added shops list.

Did you perhaps mean to do the join on the shop name:

SELECT AddedShops.shop _name, AddedShops.addr ess_1, AddedShops.city ,
AddedShops.stat e, AddedShops.zip_ code, AddedShops.tele phone
FROM AddedShops LEFT JOIN OriginalShops ON AddedShops.shop _name =
OriginalShops.s hop_name
WHERE (((OriginalShop s.shop_name) Is Null));

Now THAT SQL would bring you back a list of shops in the added shops list
for which there is no match in the original shops list, based solely on
shop_name. Of course, if the shop_name is typed even slightly differently
in the two lists, the added shop will not be matched, even though (from a
user viewpoint) that shop is in the original list.

[Rick Wannall]

I wasn't able to get a LEFT JOIN to work with any variation on LIKE that I
could think of. Someone else may know how to do that.

Meanwhile, here is another way to go at it. Use a subquery to count matches
in the original table. I based this test on finding the added shop's name
string anywhere in the original shop's name string. You might want to
compare only the left characters up to the limit of the added shop's name
(essentially what you're trying with the LIKE approach in the JOIN).

SELECT Shops_Added.Sho pName, (Select Count(*) From shops_original where
instr(1, shops_original. shopname, shops_added.sho pname)<> 0) AS Matches
FROM Shops_Added;

My instinct tells me that when you start having problems like this it's time
for me to ask a question like this: What is the situation that dictates
that you must be able to add records in another table but find out based on
string comparisons whether or not you already have the same record in
another table? Especially where you have no control over the string field
entries on which you're attempting your match??