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