Comparing multiple fields from 2 files uing awk

Hi
I have 2 files as below
File 1
Chr Start End
chr1 120 130
chr1 140 150
chr2 130 140

File2
Chr Start End Value
chr1 121 128 ABC
chr1 144 149 XYZ
chr2 120 129 PQR

I would like to compare these files using awk; specifically if column 1 of file1 is equal to column 1 of file2 (and column 2 of file 1 if less than column 2 of file2 and column3 of file1 is greater than column 3 of file2). If all 3 conditions satisfy then extract the entire line of file2.

I know that one of file has to be loaded into an array, but just dont know how to index all the fields.

Any help appreciated.
Thanks

Have a go with this:

awk '
    NR == 1 { getline <f2 buf; next; }  # skip header record (remove this line if no header)

    {
        getline buf <f2;
        split( buf, a, " " );
        if( $1 == a[1]  && $2+0 < a[2]+0 && $3 > a[3] )
            printf( "%s\n", buf );
    }
' f2="file-2" file-1 

This assumes that there are the exact same number of lines in each file, and that records align between both files.

Thanks a lot!!!! Your code worked perfectly for my test file.
However the file sizes are not the same. Also there is no one to one correspondence between the lines in the 2 files.
I would like each row of file1 to search through every row of file 2 and return all values in file 2 which were within the range of the single line in file 1.
For example:
1st row of file1
chr1 201 301

File2 (all rows)
chr1 220 230 abc
chr2 400 500 xyz
chr1 290 300 pqr

Result
chr1 201 301 abc
chr1 201 301 pqr

since both of these are within the range of 2 rows in file2. Therefore I was thinking of loading the file2 in an array and then looping over this array with each row of file1.

I would greatly appreciate any input.
Thanks once again.

You have provided contradictory information.

But your sample output in the following is not extracting the entire line from file2. It is using the entire line from file1 with the final field in file2 appended.

Regards,
Alister

---------- Post updated at 01:42 PM ---------- Previous update was at 01:38 PM ----------

Assuming that your sample output in your most recent post is accurate ...

join f1 f2 | awk '$2<$4+0 && $3>$5+0 {print $1,$2,$3,$6}'

... will do the job if you sort f1 and f2.

Regards,
Alister

Thanks Allister!!
Apologize for the mistake in my earlier post. Yes the most recent output example is the desired output.
I am not very familiar with awk and therefore do not understand what +0 does in this statement :$2<$4+0
Thanks

---------- Post updated at 01:04 PM ---------- Previous update was at 12:57 PM ----------

"Also, why is the highlighted line included in the result? Column 3 in file1 is not greater than column 3 in file2."

Allister, like I had mentioned I was looking to search through the entire file2, not just the corresponding lines in both files.
Therefore first line of file1 is repeated, since it overlaps (ie, $2 (file1)<$2(file2) and $3(file1)> $3(file2).

The addition coerces one of the operands to numeric type. This, along with the numeric nature of the strings in your fields, ensures that the comparison is done numerically and not lexicographically. If the comparison were done using string types, then 2 is greater than 100, instead of less.

Regards,
Alister

---------- Post updated at 02:08 PM ---------- Previous update was at 02:06 PM ----------

I only had that up there for about two minutes or so. It was the result of a minor brain aneurysm on my part. Column 3 in file1 obviously is greater than column 3 in file2. Disregard it and let us never speak of this again.

Regards,
Alister

Thanks for explaining Allister.

Hi
Wondering if anyone has the answer for my question posted above.
I am trying to compare 2 files; such that file sizes are not the same.
Each line of file1 had to compared to each line of file2. I know how to do it perl using 2 loops: load file2 in an array and loop through each line of file2 using 1st line of file1. However I am not able to do this in awk.
@agama "This assumes that there are the exact same number of lines in each file, and that records align between both files." this assumption is not true.
Thanks