compare two fields in two tables

[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

 

[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

 

[Peter]

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

// Peter

 

[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

 

[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