Comapare the 1st Column of one file with the all column of 2nd file

Question

I want to compare the 1st column of one file with all columns of the second file and if match found print the first column (1st file) and the complete row where match found in the second file.

Example Input file_1

RAM_1
RAM_2
RAM_3
RAM_4
RAM_5
RAM_6

Example Input file_2

RAM_7 RAM_3
RAM_8 RAM_10 RAM_15 RAM_2
RAM_6 RAM_16 RAM_4
RAM_11 RAM_5 RAM_18 RAM_20 RAM_19
RAM_1 RAM_8 RAM_9 RAM_12

Expected Output

RAM_1  RAM_1 RAM_8 RAM_9 RAM_12
RAM_2  RAM_8 RAM_10 RAM_15 RAM_2
RAM_3  RAM_7 RAM_3
RAM_4  RAM_6 RAM_16 RAM_4
RAM_5  RAM_11 RAM_5 RAM_18 RAM_20 RAM_19
RAM_6  RAM_6 RAM_16 RAM_4

I have tried for fix number of column but it print only fist line of file.

 awk 'NR==FNR{a[$1]=$0} $1 in a && $2 in a && $3 in a{print a[$1] ORS a[$2] ORS a[$3]}' file_2 file_1

Answer 1

Could you please try following, based on shown samples only, written in GNU awk.

awk '
FNR==NR{
  a[$0]=$0
  next
}
{
  for(i=1;i<=NF;i++){
    if($i in a){
      print a[$i],$0 | "sort -k1"
    }
  }
}' file1  file2

Explanation: Adding detailed explanation for above.

awk '                                  ##Starting awk program from here.
FNR==NR{                               ##Checking condition FNR==NR which will be TRUE when Input_file1 is being read.
  a[$0]=$0                             ##Creating an array named a with index current line and its value is current line.
  next                                 ##next will skip all further statements from here.
}
{
  for(i=1;i<=NF;i++){                  ##Going through all fields here in current line.
    if($i in a){                       ##checking condition if current field is inside array then do following.
      print a[$i],$0 | "sort -k1"      ##Printing value of array a with index of current field then current line printing here and sorting it by first field.
    }
  }
}' file1  file2                        ##Mentioning Input_file names here.

Answer 2

This might work for you (GNU sed):

sed -E '1{x;s/^/cat file2/e;x};G;s/^(\S+)(\n.*)*\n([^\n]*\<\1\>[^\n]*).*/\1 \3/;P;d' file1

At the beginning of file1 copy file2 into the hold space.

For each line in file1, append file2 and using pattern matching and back references, either generate a line containing the first column from file1 and its matching line from file2 or just print the original line.

Answer 3

Another way to do it, assuming word boundaries are enough to avoid partial matches and the text to match doesn't have regex metacharacters:

$ awk 'NR==FNR{a[$0]; next} {for(k in a) if(k ~ "\\<"$1"\\>") print $0, k}' f2 f1
RAM_1 RAM_1 RAM_8 RAM_9 RAM_12
RAM_2 RAM_8 RAM_10 RAM_15 RAM_2
RAM_3 RAM_7 RAM_3
RAM_4 RAM_6 RAM_16 RAM_4
RAM_5 RAM_11 RAM_5 RAM_18 RAM_20 RAM_19
RAM_6 RAM_6 RAM_16 RAM_4