compare two fields in two tables

P

Peter

Hi

I got two tables and I want to compare two fields in each table and get the
result like:

- Values that exists in both tables.
- Values that not exists in both tables.

How can I solve this problem?

//peter
 
T

Tom Ellison

Dear Peter:

I hate to have to get technical, but this is a technical question in a
technical forum, so here goes.

Do you want to compare two fields in each table? Or do you want to
compare one field from each table? I'm going to assume you really
mean the latter. Maybe I'm wrong. But it makes a big difference in
my answer.

Now I'm also going to assume the column in each table is not indexed,
so a JOIN isn't going to be particularly fast anyway. So I'm adopting
a syntax that is easier to read, but would not be as efficient as a
JOIN if the columns were indexed.

SELECT DISTINCT Column1
FROM Table1
WHERE Column1 IN (SELECT Column2 FROM Table2)

This will show a list of all values found in the subject columns of
both tables.

SELECT DISTINCT Column1
FROM Table1
WHERE Column1 NOT IN (SELECT Column2 FROM Table2)

This will show all the values from Table1 not found in Table2.

SELECT DISTINCT Column2
FROM Table2
WHERE Column2 NOT IN (SELECT Column1 FROM Table1)

And vice versa.

Now combining these last two:

SELECT DISTINCT Column1
FROM Table1
WHERE Column1 NOT IN (SELECT Column2 FROM Table2)
UNION ALL
SELECT DISTINCT Column2
FROM Table2
WHERE Column2 NOT IN (SELECT Column1 FROM Table1)

There's a list of all the values found in only one of the two tables.
No duplicates, even if a value occurs 5 times in Table1 but not in
Table2.

Hope this came close to what you meant.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
P

Peter

hmm...
What I mean is. I got one field in each table, and I want to compare them.

// Peter
 
T

Tom Ellison

Dear Peter:

Well, I don't mean to be nasty. I'm just trying to get it right.
After several year's answering questions like yours, I have become
quite aware when I'm making assumptions and interpretations, and it is
my habit to spell out those assumptions and interpretations to try to
minimize misunderstandings. Otherwise I might confuse you far more
than you confuse me! So I mean it as a favor to you to carefully
describe the problem I aim to solve.

So, if my assumptions turned out to be correct, did the suggestions I
made help you any?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
P

Peter

Oh that's OK. I sended my message right after the first one but your
response were quicker.

Thanks for all help, that helped alot.

// Peter
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top