There's no real need to write such a complicated script for this though, simple use of unix command line tools are enough.

Firstly, verify that the files are indeed sorted on the third and second column resp with these commands. 

sort -ck3 file1.db
sort -ck2 file2.query

[download]

We get no error, so they are sorted. (Note that we're doing a textual comparision, not a numerical one, because join can't handle the latter.)

Then let's use the join command to join the two files. 

join -a 2 -1 3 -2 2 -e - -o 2.1,2.2,2.3,2.4,2.5,2.6,2.7,2.8,1.1 file1.
+db file2.query | tee result

[download]

Admittedly the command-line looks complicated because we use just about any option join has, but we're lucky because we don't need any extra features join doesn't have. (If you have numbers in the join column that aren't of the same width, then you're out of luck here and likely need at least a simple perl script.)

The output is this. 

1190 31277 A > T 1 0 0 -
1190 31607 C > A 0 3 1 -
1190 31629 C > T 0 2 0 -
1190 31789 A > G 1 2 5 zm1829427
1190 31882 A > C 0 4 0 -
1190 31883 T > A 0 4 0 zm445312
1190 31883 T > C 2 2 5 zm445312
1190 32199 C > T 0 1 1 -
1190 32487 T > C 0 1 1 -
1190 32496 A > G 0 3 0 -

[download]

The whitespaces separating the fields are destroyed, but if that bothers you you can fix it up easily with this command. 

(sed 's/^\([^ ]*\) \([^ ]*\) \([^ ]* [^ ]* [^ ]*\) \([^ ]*\) \([^ ]*\)
+ \([^ ]*\)\(.*\)/\1    \2    \3    \4    \5    \6    \7/' result; ech
+o; echo -n "Total number of HITS: "; grep -cv ' -$' result) |tee resu
+lt.fmt

[download]

The result is this: 

1190    31277    A > T    1    0    0     -
1190    31607    C > A    0    3    1     -
1190    31629    C > T    0    2    0     -
1190    31789    A > G    1    2    5     zm1829427
1190    31882    A > C    0    4    0     -
1190    31883    T > A    0    4    0     zm445312
1190    31883    T > C    2    2    5     zm445312
1190    32199    C > T    0    1    1     -
1190    32487    T > C    0    1    1     -
1190    32496    A > G    0    3    0     -

Total number of HITS: 3

[download]
Update 2009 sep 2.

See Re^2: Joining two files on common field for a list of other nodes where unix textutils is suggested to merge files.

In reply to Re: duplicates getting omitted while comparing values inside foreach. by ambrus
in thread duplicates getting omitted while comparing values inside foreach. by patric

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.