added and removed IDs from 2 tables

[markgraham123]

Hi all,

I'm working on a req. where i have 2 tables:

Table1:                        Table2:                                     

ID, Name                 ID, Name            

1,A                                1,A

2,B                                 2,B

3,C                                 3,C

4,D                                 6,F

5,E                                  7,G

     In the output i want to see

newly added records as :

ID, Name

6,F

7,G

Removed records as:

ID, Name

4,D

5,E

[sunny_talwar]

You only want to see the new and old? and get rid of the other stuff or do you still want to be able to keep all the things?

[markgraham123]

Sunny,

I want to see all the Table1 records and Table2 records.

In the end, i need New and old records in separate fields.

[sunny_talwar]

May be this:

Table1:

LOAD * Inline

[

ID, Name

1,A

2,B

3,C

4,D

5,E

];

TempTable2:

NoConcatenate

LOAD ID as ID1,

  Name

Inline

[

ID, Name

1,A

2,B

3,C

6,F

7,G

];

Table3:

LOAD ID1 as AddedID,

  Name as AddedName

Resident TempTable2

Where not Exists(ID, ID1);

Table4:

LOAD ID as RemovedID,

  Name as RemovedName

Resident Table1

Where not Exists(ID1, ID);

Table2:

NoConcatenate

LOAD ID1 as ID,

  Name

Resident TempTable2;

DROP Table TempTable2;

[markgraham123]

Sunny,

This is what i'm exactly looking for...

I have a question here..

While doing the resident load in Table3 from resident TempTable2.....i do not see any filed ID in the TempTable2 table.

But how are we specifiying in where condition...

Can you please tell.

[sunny_talwar · Accepted solution]

Its the Where Exists statement which checks for a value in another field elsewhere loaded before this table's load. So ID comes from Table1 and it's checking against ID1 -> Where not Exists(ID, ID1)

[markgraham123]

Perfecto Sunny !!