Outer Join on multple columns with some null values

[PC]

Hi all

I am trying to join two tables in the data model based on two columns that are present in both tables, ID_1 and ID_2. In table 1 the column ID_1 has no null values, but can have null values in ID_2 and vice versa for table 2.

A non null set may be present in one table but one is null in the other table (e.g. ID_1 and ID_2 not null for a record in table 2 but ID_2 is null in table 1). If I use regular OUTER JOIN I would get this record as a double up, I don't want that to happen.

Is there a simple way of getting the correct output?

Cheers

[Taoufiq_Zarra · Accepted solution]

@PC  One solution using Lookup

Table1:

load * inline [
ID_1,ID_2
101,203
102,
103,209
104,210
105,
106,202
107,
108,
109,
110,206
];

Table2:

load ID_1,ID_2 as ID_2_tmp inline [
ID_1,ID_2
109,201
,202
101,203
107,204
,205
,206
,207
,208
103,209
,210
];

Tmp:
noconcatenate
load ID_1,if(isnull(lookup('ID_2_tmp','ID_1',ID_1,'Table2')),ID_2,lookup('ID_2_tmp','ID_1',ID_1,'Table2')) as ID_2 resident Table1;

load ID_2_tmp as ID_2,if(isnull(lookup('ID_1','ID_2',ID_2_tmp,'Table1')),ID_1,lookup('ID_1','ID_2',ID_2_tmp,'Table1')) as ID_1 resident Table2;


drop tables Table1,Table2;


Final:
noconcatenate
load distinct ID_1,ID_2 resident Tmp;

drop tables Tmp;

output:

[Kushal_Chawda]

@PC  try below

Table1:
load * inline [
ID_1,ID_2
101,203
102,
103,209
104,210
105,
106,202
107,
108,
109,
110,206
];

Join

load * inline [
ID_1,ID_2
109,201
,202
101,203
107,204
,205
,206
,207
,208
103,209
,210
] where not Exists(ID_2);

[PC]

Thank you Taufiq

This seems to work

[PC]

Thank you Kush, but this does not work. Method above seems to do the trick though.