Special join

[#1]

Hello everyone,

we have 2 tables:

tableNewNumbers:

number	state
123	active
234	active

tableOldNumbers:

number	state
234	deleted
888	active

The state of all numbers in "tableOldNumbers" which do not exist in "tableNewNumbers" should be changed to 'removed'.

Our result table should be:

number	state
123	active
234	active
888	removed

[Jason_Michaelides · Accepted solution]

Data:

LOAD

     Number

     ,State

FROM Table1....;

CONCATENATE (Data)

LOAD

     Number

     ,'removed'     AS     State

FROM Table2

WHERE NOT EXISTS(Number);

Hope this helps,

Jason

[#3]

Thanks for your quick answer.

This was also our idea, but the field "state" has already a value in Table2 which has to be updated.

The result of your advice is:

number	state
123	active
234	active
234	deleted
888	active

[#4]

Hi Sleepa,

   what  Jason Michaelides suggested is working fine .

Kindly find the attached application.

Regards,

Sampath Kumar G

[Jason_Michaelides]

Are you sure you are loading tableNewNumbers first?  To use your exact table names in my script:

Data:

LOAD

     Number

     ,State

FROM tableNewNumbers....;

CONCATENATE (Data)

LOAD

     Number

     ,'removed'     AS     State

FROM tableOldNumbers

WHERE NOT EXISTS(Number);

If 234 existis already in tableNewNumbers it will not be loaded from tableOldNumbers.  Likewise, if 888 does not exist in tableNewNumbers then it will be loaded from tableOldNumbers.

Jason

[#6]

Slight modification. If in the real world you are trying to find out how to join two tables because you are pulling in some different fields then you can do this. Otherwise, if the tables are the same then Jason's way is fine.

Data:

LOAD

     Number

     ,State

FROM Table1....;

LEFT JOIN LOAD

     Number

     ,State

     ,SomeOtherField

FROM Table2;

CONCATENATE (Data)

LOAD

     Number

     ,'removed'     AS     State

     ,SomeOtherField

FROM Table2

WHERE NOT EXISTS(Number);

Seems pretty strange wanting to join on state though, maybe this isn't what you're really trying to do.

[#7]

Hi Sleepa,

What Jason and Sampath said is right. I also tried that and i got the perfect output as per yours. Check it out once again.

Regards,

Arun Prasadh.N.

[#8]

THANKS!!!

Our problem was that we got a different result when Table2 was already loaded.

Table2:

LOAD Number,

     State

From Table2;

Data:

LOAD Number,

     State

FROM Table1;

CONCATENATE

LOAD Number,

'removed' as State

RESIDENT Table2

WHERE NOT EXISTS(Number);

Now we concatenate while loading Table2 the first time. Then it is correct.

[Jason_Michaelides]

Yes - EXISTS checks back for that field in any previous section of the script.