Two Tables

[MHendler]

I have two simple tables with 2 fields each. I want to compare these tables and just get the entry(ies) that exists in the second table and does not exist in the first table.

Is there a way to do that?

Regards,

MHendler

 

[noddy]

What are you using to access the data?

If MS Access for example, you can use the "not in" as part of the query.

The example I have in a book SQL Fundamentals (ISBN 0-13-096016-0) gives the following:
*************************************
select a.number_2,
a.word_2
from twos a
where (number_2 & a.word_2) not in (select (b.number_2 & b.word_2) from twos_modified b);
**************************************

twos = first table
twos_modified = second table

You can also do it using the UNION where you get the data from table1 and then table2 with a text column to show the source, e.g.
*********************************
select number_1,
word_1,
date_1,
'data from first table' as source_of_the_data
from First_table
union
select number_2,
word_2,
date_2,
'data from second table' as source_of_the_data
from second_table
order by number_1;
***********************************
Notes:
only unique records will be in the result and you will be able to see which records are from which table.

both table must have the same data types

Hope this helps.
Noddy

 

[rush]

Can you give a little more detail. Do the two tables have the same types of fields? Do you want both fields to be 'unmatched' or either one? Any indexes on the fields in each table?

 

[RFRAN]

THE OPERATOR "UNION" RETURNS ALL DISTINCT ROWS BY EITHER OF THE QUERIES IT APPLIES TO.

EXAMPLE:

SELECT ENAME, JOB
FROM EMP_1
UNION
SELECT ENAME, JOB
FROM EMP_2
ORDER BY ENAME;