Join ?

[#1]

I have two tables , TableA & TableB

I want the all the records in TableA but exclude the common records in both the Tables.

[its_anandrjs]

Load table like this

TableA:

LOAD * Inline

[

C1

A

B

C

];

TableB:

LOAD * Inline

[

C2

A

B

E

F

] Where not Exists(C1,C2);

[sujeetsingh]

Table1:

LOAD * INLINE [

    ID, Sales

    1, 23

    2, 4

    3, 6

    4, 9

    5, 12

    6, 9

];

Table2:NoConcatenate

LOAD * INLINE [

    ID, Sales

    1, 23

    2, 4

    13, 6

    14, 9

    7, 12

    9, 9

];

Join(Table1)

LOAD

ID, Sales

,ID as ID2

Resident Table2;

DROP Table Table2;

MainData:NoConcatenate

LOAD

ID, Sales

Resident Table1 where IsNull(ID2)<>0 ;

DROP Table Table1;

[jagan]

Hi,

You can use Where not Exists() as Anand suggested, if not then attach some sample data and expected output.

Regards,

Jagan.

[its_anandrjs]

Load the tables like below script this will remove the duplicate records from your tables means common records and keep only uncommon records

Temp:

LOAD *,'Tab1' as Flag;

LOAD * Inline

[ID

A

B

C

F];

Join

TableB:

LOAD *,'Tab2' as Flag;

LOAD * Inline

[ID

A

B

C

D

E];

NoConcatenate

Temp2:

LOAD

ID,

Count(Flag) as Flag

Resident Temp

Group By ID

Order By ID;

Final:

LOAD

ID

Resident Temp2

Where Flag=1;

DROP Tables Temp,Temp2;

Output you get

ID

D

E

F

which is uncommon it both tables.

[anbu1984]

In Sql

Select data from TableA excluding common records between TableA and TableB

Select col1,col2 from TableA

Minus

select col1,col2 from TableB

[#7]

Hii,

Simply just use the function not Exist().

If not then you can try the other methods as well.