Pig Join - How to join two table with multiple fields where one of the field in key is optional?
<p>Problem: Pig Beginner- Below is my two input table</p>

<pre><code>Table 1: Contain 3 columns (VID, TID and USID)
v1 TID101 US101 
v2 TID102 
v3 TID103 
v4 TID104 US104 
v5        US105 
v6        US106 
</code></pre>

<p></p>

<pre><code>Table 2: Contain 3 columns (PID, TID, USID)
p1 TID101 US101
p2 TID102 US102
p3 TID103 US103
p4 TID104 US104
p5 TID105 US105
</code></pre>

<p>I would like to join table 1 and 2 and get output as below: </p>

<pre><code>Expected Output:
v1 TID101 US101 p1
v2 TID102       p2
v3 TID103       p3
v4 TID104 US104 p4
v5        US105 p5
</code></pre>

<p></p>

<p>I tried inner join as below: </p>

<pre><code>a= JOIN table1 BY (TID, USID), table2 BY (TID, USID);
b= FOREACH a GENERATE table1::vID, table1::TID, table1::USID, table2::PID;
</code></pre>

<p>But I get only below output:
</p>

<pre><code>Actual Output:
v1 TID101 US101 p1
v4 TID104 US104 p4
</code></pre>

<p>I could try left outer join but I feel that when I join by multiple keys, both keys are considered mandatory for joining and I cannot have "OR" condition. All I am trying is to get the PID from table2 if table1 record contain USID or TID. I am not sure what I miss and be interested to understand the best approach to arrive at the expected output. Please help!</p>