'Comparing two columns in two files using awk

I'm trying to compare two different files, let's say "file1" and "file2", in this way. If fields $2 and $3 are the same in both files, print $0 of file2. Here's an example:

file1
E 352 697 
E 25 692
E 510 744

file2
E 335 705 1 1
E 267 792 1 2
E 365 395 1 3
E 25 692 1 4
E 566 624 1 5
E 227 358 1 6
E 516 554 1 7
E 510 744 1 8
E 234 790 1 9
E 352 697 1 10

Desired output:

E 352 697 1 10
E 25 692 1 4
E 510 744 1 8

Notice that all couples $2,$3 in file1 are contained in file2 and the number of rows of the output file is the same of file1. There are a lot of questions with similar problems, I know that, but all the answers were not useful. I have tried to use:

awk 'FNR==NR {a[$2]; b[$3]; next} $2 in a && $3 in b' file1 file2 > output

It works but in the output file there are extra rows. Could you help me? Thanks!

awk


Solution 1:[1]

awk 'NR==FNR{a[$2,$3];next} ($2,$3) in a' file1 file2

Solution 2:[2]

This awk should do:

awk 'FNR==NR {a[$0];next} {for (i in a) if ($0~i) print}' file1 file2
E 25 692 1 4
E 510 744 1 8
E 352 697 1 10

It store the file1 in array a. Then loop trough file2 and test if it contains the data from array a, if yes, print the line.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ed Morton
Solution 2