Compare 2 tables and tag fields depending if present in first,second or both tables

[mazacini]

I have 2 tables. TableA and TableB. Table B is later version of TableA.

There are three fields in each table, F1, F2 and F3. When combined together (F1&F2&F3), they represent a unique field for each record in the tables. Otherwise the records are not unique.

I want to create a table with field F1&F2&F3 and an additional field "Status" which indicates

A Only - on TableA, Not on TableB

B Only - on TableB, Not on TableA,

A&B - On both TableA and TableB

Any ideas?

Thanks

[mazacini · Accepted solution]

Ok. I sorted myself.

//Load all Items from TableB

Temp1:

Load F1&F2&F3 as New1 from TableB;

//Now load items on TableA, Not on Table B

Load F1&F2&F3 as New2,

'A Only' as Status

from TableA

where not exists (New1,F1&F2&F3)

//Drop Table B Items - this leaves TableA Items not on TableB

Drop Temp1

//Now load the rest of TableA (these will be items on TableB)

Load F1&F2&F3 as New2,

'A&B' as Status

from TableA

where not exists (New2,F1&F2&F3);

//Now load TableB items not already loaded

Load F1&F2&F3 as New2,

'B Only' as Status

from Table B

where not exists (New2,F1&F2&F3)