[SOLVED] How to merge two files based on one column and print both matching and non-matching?

Issue

I am trying to merge two files, match them on $1,and display the match from file 2 into a new column $3. If they don't match, the value should display 0.

For instance i have the below two files

tmptest1.txt

aaa 231
bbb 132
ccc 111

tmptest2.txt

aaa 222
ddd 132

I'd like the output file to be:

aaa 231 222
bbb 132 0
ccc 111 0
ddd 0 132

I tried the below code, while it does append the matching number into $3, it omits non matching ones as my print statement only looks at one file. How can i consider a combination of both files?

awk 'FNR==NR{a[$1]=$2;next} {print $1,$2,a[$1]}' tmptest1.txt tmptest2.txt | sort | awk '!$3{ $3="0"1}

Result

aaa 231 222
bbb 132 0
ccc 111 0

Solution

Assuming your real files are sorted like your samples are:

$ join -o 0,1.2,2.2 -e0 -a1 -a2 tmptest1.txt tmptest2.txt
aaa 231 222
bbb 132 0
ccc 111 0
ddd 0 132

If not sorted and using bash, zsh, ksh93 or another shell that understands <(command) redirection:

join -o 0,1.2,2.2 -e0 -a1 -a2 <(sort temptest1.txt) <(sort tmptest2.txt)

Answered By - Shawn