join rows based on string in 2 columns of 2 files

Question

I have a file1 with 3 columns and file2 with 4 columns on a Linux system. How do I join the two files based on the string in column 3 of file1 to the string in column 2 of file2? File2 is a large db with many entries. Columns 3 and 2 of file1 and file2 respectively share only few strings. I would like to output file1 with joined file2 rows in case the strings match and output a dash for entries with no matches.

file1:

300 100 a101
450 410 a400
670 710 a20
700 610 a340

file2:

b30  a340 tttttttt 456
b500 a200 llllllll 567
b60  a101 uuuuuuuu 344
b40  a50  kkkkkkkk 223

output:

300 100 a101 b60 a101 uuuuuuuu 344
450 410 a400 -
670 710 a20  -
700 610 a340 b30 a340 tttttttt 456

Answer 1

Using GNU awk and GNU join, which are standard on Linux (may or may not work with non-GNU versions):

$ join -a1 -1 3 -2 2 <(sort -k3,3 file1) <(sort -k2,2 file2) | 
    awk '$4 == "" { $4 = "-" }; {t=$1; $1=$2; $2=$3; $3=t; print}' |
    sort
300 100 a101 b60 uuuuuuuu 344
450 410 a400 -
670 710 a20 -
700 610 a340 b30 tttttttt 456

The join command joins file1 and file2 on fields 3 and 2 respectively. It uses process substitution to ensure that both files are sorted by their respective key fields. The -a 1 option is used so that all lines from file1 are printed, even if they are not matched with a line from file2.

Unfortunately, join places file1's key field at the start of each record. This is fixed with awk to move the fields back to their original order, using a variable called t as a temporary holder for the value of $1. The awk script also adds the trailing dash character in field $4 if there was no match between the files (because join itself does not do this).

Finally, the output is sorted.