473,320 Members | 2,088 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to find Unique records?

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.address_1, AddedShops.city,
AddedShops.state, AddedShops.zip_code, AddedShops.telephone
FROM AddedShops LEFT JOIN OriginalShops ON AddedShops.telephone =
OriginalShops.[PHONE NUMBER]
WHERE (((OriginalShops.[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.

Jun 1 '06 #1
3 2555
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.address_1, AddedShops.city,
AddedShops.state, AddedShops.zip_code, AddedShops.telephone
FROM AddedShops LEFT JOIN OriginalShops ON AddedShops.shop_name =
OriginalShops.shop_name
WHERE (((OriginalShops.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.
Jun 1 '06 #2
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.address_1, AddedShops.city,
AddedShops.state, AddedShops.zip_code, AddedShops.telephone
FROM AddedShops LEFT JOIN OriginalShops ON AddedShops.shop_name =
OriginalShops.shop_name
WHERE (((OriginalShops.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.


Jun 1 '06 #3
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.ShopName, (Select Count(*) From shops_original where
instr(1, shops_original.shopname, shops_added.shopname)<> 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??
Jun 1 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
by: Justin Hoffman | last post by:
This is a question concerning query optimisation. Sorry if it's a bit long, but thanks to anyone who has the patience to help - This is my first post here... If I have two tables:...
3
by: Phil | last post by:
I am looking to set up a hyperlink control on a form to retrieve letters that correspond to a record on a form. That is, there may be 100 form records, and I would like each of those form records...
3
by: N J | last post by:
Hi, I want my form to tell me if there are similar records based on a certain field. For example, I currently have a record open on my form with 123@abc.com as a data in a field. I want the form...
9
by: tshad | last post by:
How do I find (and set) a couple of labels in the Footer after a DataGrid is filled? I have a bunch of DataGrids that get displayed nested inside a DataList. The datagrid looks like: ...
2
by: D. Dante Lorenso | last post by:
I'm trying to build a table that will store a history of records by enumerating the records. I want the newest record to always be number ZERO, so I created a trigger on my table to handle the...
2
by: ET | last post by:
Hi! I'll need help on what todo with this information in table... There has to be a unique on one column (Item ID, for inventory purpose) but in many cases that ID is not known... thus, many...
7
by: john | last post by:
In my form I have a master table and a details table linked 1xM. I can search through the whole parent table but I also like to be able to search through the child table fields to find parent...
1
by: aknoch | last post by:
My basic situation is this - I ONLY want duplicates, so the opposite of DISTINCT: I have two tables. Ordinarily, Table1ColumnA corresponds in a one to one ratio with Table2ColumnB through a...
2
by: srusskinyon | last post by:
I need some help getting unique records from our database! I work for a small non-profit homeless shelter. We keep track of guest information as well as what services we have offered for...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.