How to get Uncommon rows from 2 tables ?

[nikhilgarg]

Hey,

Let say i have following 2 tables and i want only UNcommon rows from both tables.

How to get it  ?

Table1 :

LOAD * INLINE [

    Table1_Id, Value

    1, 100

    2, 200

    3, 300

    4, 400

    5, 500

];

Table2 :

LOAD * INLINE [

    Table1_Id, Value1

    1, 1000

    1, 1000

    3, 3000

    5, 5000

    6, 6000

    7, 7000

] ;

[#2]

Hi nikhil,

Use where not exists(field_name) at the end of the load statement of tab2.

hope it will get you the unmatched records in two tables.

Regards,

Barathiraja

[nikhilgarg]

HEy ,

I used following script but it is giving me error after Not Exists statement on FiledName:

Table1 :

LOAD * INLINE [

    Table1_Id, Value

    1, 100

    2, 200

    3, 300

    4, 400

    5, 500

];

Join

LOAD * INLINE [

    Table1_Id, Value1

    1, 1000

    1, 1000

    3, 3000

    5, 5000

    6, 6000

    7, 7000

] Where not Exists Table1_Id ;

[#4]

hi,

use like this where not exists (Table1_ID);

[NareshGuntur · Accepted solution]

Hi Nikhil,

Please use the below script.

The final output is

Table1_Id	Value	Value1
2	200
4	400
6		6000
7		7000

Table1 :

LOAD * INLINE [

    Table1_Id, Value

    1, 100

    2, 200

    3, 300

    4, 400

    5, 500

];

Join

Table2 :

LOAD * INLINE [

    Table1_Id, Value1

    1, 1000

    1, 1000

    3, 3000

    5, 5000

    6, 6000

    7, 7000

] ;

NoConcatenate

Table3:

LOAD *

Resident Table1

Where IsNull(Value) or IsNull(Value1);

DROP Table Table1;

Cheers,

Naresh