Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)