match two columns from one file to three columns from another file, print out lines from the file with two columns

Question

I have file A with two columns, it looks like this:

7000000185249100 162280
7000000185249048 235500
7000000185249052 755361
7000000185249068 427550
7000000185249070 269102
7000000185249081 291122

And I have file B with three columns, it looks like this:

7000000185249100 1622651 1623044
7000000185249048 235104 235805
7000000185249146 2500324 2502635
7000000185249100 1218818 1221734
7000000185249468 88587 89699
7000000185249239 299691 300277
7000000185249315 769635 769986
7000000185249374 1548986 1549747

So what I wanted to do is to print out lines from file A,

1. if the number in first column in file A matches the number in first column in file B, and
2. the number in the second column in file A is within the range of the numbers in the second and third columns in file B.

Expected output will be:

7000000185249048 235500

I tried with the following code, but failed.

awk -F '\t' 'FNR==NR{a[$1,$2,$3]=$0;next}{if(b=a[$1, >=$2 && <= $3]){print b}}' file B file A

Answer 1

1. Use join to find common 1st fields, then use bash to compare values:

   join --nocheck-order -j 1 A B | 
   while read a b c d ; do 
       [[ ( b -le d && b -ge c ) || ( b -le c && b -ge d ) ]] && echo $a $b
   done 
   

   The OP spec states "the number in the second column in file A is within the range of the numbers in the second and third columns in file B". This range might not ordered, so the logic between [[ and ]] handles it either way. Example:

   • If A2=3, B2=2, and B3=4, that's matched by ( b -le d && b -ge c ).
   • If A2=3, B2=4, and B3=2, that's matched by ( b -le c && b -ge d ).

2. Not so good GNU sed code to turn each line of file B into two piped numgrep commands that search file A for ranges, then evaluate the commands. Because the resulting list of commands might have redundant output, pipe that to awk to perform an unsorted uniq:

       sed -n \
       's#\(\w*\)\W*\(\w*\)\W*\(\w*\)#numgrep /\1/ A\|numgrep /\2..\3,\3..\2/#e
        /./p' B |
       awk '!a[$0]++'
   

Output of either method:

7000000185249048 235500