AWK merging col1 with col2

say I have a file (file1 - 3 cols) and a cross reference file (xref - 2 cols) and I want to recode all 3 cols of file1 using col1 as key of the cross ref file.

file1
1 4 5
2 3 1
3 4 2

xref
1 11
2 21
3 31
4 41
5 51

I can recode the first col of file1 with the first col of xref with

awk 'NR==FNR{a[$1]=$2;next} ($1 in a) {print $0, a[$1]}' OFS=" " xref file1 > file2

output file2
1 4 5 11
2 3 1 21
3 4 2 31

but now I want to recode col2 of file1 still with col1 of xref as key
desired output
1 4 5 11 41
2 3 1 21 31
3 4 2 31 41

and then recode col3 of file1 still with col1 of xref as key
desired output
1 4 5 11 41 51
2 3 1 21 31 11
3 4 2 31 41 21

pfff - I hope you get the gist of that - many thanks!

Solution

With your shown samples please try following awk code.

awk 'FNR==NR{arr[$1]=$2;next} ($1 in arr){print $0,arr[$1],arr[$2],arr[$3]}' xref  file1

OR adding a non-one liner form of above code:

awk '
FNR==NR{
  arr[$1]=$2
  next
}
($1 in arr){
  print $0,arr[$1],arr[$2],arr[$3]
}
' xref  file1

Bonus solution: In case you have records where 2nd OR 3rd fields are not present in the other file and you want to show them as eg: N/A then try following awk code.

awk '
FNR==NR{
  arr[$1]=$2
  next
}
($1 in arr){
  print $0,arr[$1],($2 in arr?arr[$2]:"N/A"),($3 in arr?arr[$3]:"N/A")
}
' xref  file1

Explanation: Adding detailed explanation for above code.

awk '                 ##Starting awk program from here.
FNR==NR{              ##Checking condition FNR==NR which will be TRUE when xref is being read.
  arr[$1]=$2          ##Creating array arr with index of $1 and value of $2.
  next                ##next will skip all further statements from here.
}
($1 in arr){          ##Checking condition if $1 is present in arr then do following.
  print $0,arr[$1],arr[$2],arr[$3]  ##Printing current line and value of arr with index of $1 then $2 and then $3.
}
' xref  file1         ##mentioning Input_file names here.