I'm completing a join where I specify the outfile format....such as:

Now this works great. Now what I also want is to include any unmatched rows from the first file...and I use the -a flag for this.

What I get however is a null value in the field that is specifed to come from the second file for matches. You'll see in the -o I have 1.1 1.2 .....and 2.4 - so I want the fourth field to come from the second file - but obviously only when there is a match.

I'm getting no return in the 4th column now - it seems to just be going to the 2nd file to find this field even for the unmatched records....

How can I make the -a just insert the whole record when there is no match - and ignore the -o?

I realise that there are hack ways of doing this with sed and cut and others - but I want to understand joins as I'm just starting to use them.

__________________
Pete

I haven't had much love on this issue....but I have a work around - if anyone is interested.

Well I have figured out one solution....but am still curious why the join function should work this way. Would seem to be a flaw?

I got around this by using the same field reference in both files then piping this through awk, and where 2.4 was not valid using 1.4 - or when 2.4 was valid using that i.e.

join -t\| -j1 1 -j2 1 -a 1 -o 1.1 1.2 1.3 1.4 2.4 1.5 1.6 1.7 $OUTFILE TMP_INFILE |
awk -F\| '{
if ($5 ~ /^[0-9][0-9][0-9][0-9]$/) {
x = $5
} else {
x = $4
}

printf("%s|%s|%s|%s|%s|%s|%s\n", $1, $2, $3, x, $6, $7, $8);
}' > replaced

__________________
Pete