How to find duplicates from two tables, as well as find a duplicate in yourself?

Question

I created this statement in access 2003

SELECT COUNT(*) FROM TABLEA WHERE NOT EXISTS(SELECT * FROM TABLEB); 

Does this operator help check if the entries in table A match table b? TABLEA is the new table in table b, and I want to make sure that all entries from table b are shown in table A.

Secondly, I have this TABLEC table. How can I check if there are duplicate entries, that is, all field values ​​are the same, in TABLEC?

Answer 1

Answer: No, your request does not make sense.

To indicate whether two entries are “equal,” you must define the term “equal.” Should all fields be equal? Or just certain fields?

If you have two tables, TableA and TableB , and they have two fields "A" and "B", then this statement finds all the records that exist in both tables:

 select distinct TableA.* from TableA join TableB on TableA.A = TableB.A and TableA.B = TableB.B 

or

 select * from TableA where exists ( select 1 From TableB where TableA.A = TableB.A and TableA.B = TableB.B ) 

Edit: User 10e5x indicated that his table contains NULL values. Thus, comparing for each field should be a bit more complicated in order to offset the warnings about NULL comparisons.

I will just put the WHERE part:

 where TableA.A = TableB.A or coalesce (TableA.A, TableB.A) is NULL and TableA.B = TableB.B or coalesce (TableA.B, TableB.B) is NULL 

The coalesce(a,b,c...) function returns the leftmost value not NULL, therefore

 coalesce (A,B) is NULL -- is equal to A is NULL and B is NULL 

Note. This complex coding is the reason you should avoid NULL values ​​in columns used for comparison.

Answer 2

try it

SELECT documentno FROM TableA CROSSING SELECT documentno FROM TableB