Joining 2 tables

[apthansh]

Hi - I have 2 tables below as an example.

Table 1:

ID      City

A        Atlanta

B       Boston

Table 2:

ID      City

A        Atlanta

D       Dayton

When I join I want the below results.As a result it should drop ID A, as it is in both the tables.Which join I should be doing for this ?

Output:

ID     City

B       Boston

D       Dayton

[Nicole-Smith · Accepted solution]

The following code:

Table1:
LOAD ID as ID1, City as City1
INLINE [
	ID, City
	A, Atlanta
	B, Boston
];

Table2:
LOAD ID as ID2, City as City2
INLINE [
	ID, City
	A, Atlanta
	D, Dayton
];

Final:
LOAD ID1 as ID, City1 as City
RESIDENT Table1
WHERE NOT EXISTS(ID2, ID1);

CONCATENATE (Final)
LOAD ID2 as ID, City2 as City
RESIDENT Table2
WHERE NOT EXISTS(ID1, ID2);

DROP TABLES Table1, Table2;

Will produce the following output:

ID	City
B	Boston
D	Dayton

[neelamsaroha157]

You may also try this- 

Table1:
LOAD * INLINE [
ID, CityA
A, Atlanta
B, Boston
];

Table2:
LOAD * INLINE [
ID, CityB
A, Atlanta
D, Dayton

];

Temp:
NoConcatenate
LOAD Distinct ID as IDTemp Resident Table1;
Inner Join
LOAD Distinct ID as IDTemp Resident Table2;

Temp2:
NoConcatenate
LOAD Distinct ID, CityA as City Resident Table1; DROP Table Table1;
Outer Join
LOAD Distinct ID, CityB as City Resident Table2; DROP Table Table2;

Final:

NoConcatenate
LOAD * Resident Temp2 Where not Exists(IDTemp, ID); DROP Tables Temp, Temp2;

[apthansh]

Awesomeness !