Awk: matching multiple fields between 2 files

UNIX for Beginners Q & A
1

Hi,

I have 2 tab-delimited input files as follows.
file1.tab:

green	A	apple
red	B	apple

file2.tab:

apple	-	A;Z

Objective:
Return $1 of file1 if,
. $1 of file2 matches $3 of file1 and,
. any single element (separated by ";") in $3 of file2 is present in $2 of file1

In order to get:

green

The following code returns a blank output, since it seems to retain only the last iteration of file1.

gawk '
BEGIN{FS=OFS="\t"}
NR==FNR{
   letter[$3]=$2
   color[$3]=$1
   next
}
{
   a = split($3, b, ";")

   for(i=1; i<=a; i++){
      if($1 in letter && letter[$1] ~ b){
         print color[$1]
      }
   }
}' file1.tab file2.tab
2

How about

awk '
BEGIN           {FS = OFS = "\t"
                }

NR==FNR         {for (n = split ($3, T, ";"); n>0; n--) CMP[$1, T[n]]
                 next
                }

($3,$2) in CMP  {print $1
                }
' file2 file1
green
3

Thanks RudiC,

However I should have insisted on the second criteria ("$3 of file2 is present in $2 of file1"). Meaning that I am not looking for an exact match.

For example if file1.tab is:

green	A;foo	apple
red	B;bar	apple
4

Weren't that a brilliant exercise for the reader to implement?