How to display records that do not match

[#1]

Hello community!

I am needing to show records that do not match between two tables that have a couple of fields in common.

The idea is to get those records from table A without their correlation in Table B and the same in the other direction.

They could guide me on how to get the differences between the two tables.

Thank you very much.

[Gysbert_Wassenaar · Accepted solution]

Perhaps this discussion helps: How to compare 2 tables and put non-matching results?

[maxgro]

I think you can

- find the matching records

- concat the 2 tables excluding the matching

f1 and f2 are the common fields

t1:

load * inline [

f1, f2, e1

a,a,1

a,a,11

b,b,2

c,c,3

d,d,4

d,d,44

e,e,5

];

t2:

NoConcatenate load * inline [

f1, f2, e2

a,a,111

b,b,222

c,c,333

w,w,777

w,w,7777

z,z,999

];

match:

noconcatenate load f1, f2, f1 & '|' & f2 as f1f2 Resident t1;

inner join (match) load f1, f2 Resident t2;

final:

NoConcatenate

load *, 't1' as source Resident t1

where not exists (f1f2, f1 & '|' & f2); 

Concatenate (final)

load *, 't2' as source Resident t2

where not exists (f1f2, f1 & '|' & f2); 

DROP table t1, t2, match;