Problems using join for simple database lookup

Shell Programming and Scripting
1

I am trying to get a script working that will perform a simple database lookup using the join command.

Here are the two files that I am trying to join:

% cat lookup1.txt
Number_1    Other_data_a
Number_5    Other_data_b
Number_8    Other_data_c
Number_10   Other_data_d
 
% cat lookup2.txt
Number_1    ID_1    Assignment_Name_ABC
Number_2    ID_2    Assignment_Name_ABC
Number_3    ID_3    Assignment_Name_DEF
Number_4    ID_4    Assignment_Name_HIJ
Number_5    ID_5    Assignment_Name_KLM
Number_6    ID_6    Assignment_Name_NOP
Number_7    ID_7    Assignment_Name_QRS
Number_8    ID_8    Assignment_Name_TUV
Number_9    ID_9    Assignment_Name_WXY
Number_10   ID_10   Assignment_Name_Z01

My desired output is:

Number_1    ID_1    Assignment_Name_ABC    Other_data_a
Number_5    ID_5    Assignment_Name_KLM    Other_data_b
Number_8    ID_8    Assignment_Name_TUV    Other_data_c
Number_10   ID_10   Assignment_Name_Z01    Other_data_d

The command that I am trying to use to accomplish this is:
join -j1 1 -j2 1 -o 1.1 2.2 2.3 1.2 lookup1.txt lookup2.txt

I only get some of the data:

Number_1    ID_1    Assignment_Name_ABC    Other_data_a
Number_5    ID_5    Assignment_Name_KLM    Other_data_b
Number_8    ID_8    Assignment_Name_TUV    Other_data_c

When I substitute the lookup files with my real data (1st file ~ 50 lines, 2nd file ~ 500 files) I only get three lines returned as well. Any idea what I am doing wrong or other methods that will produce the desired result? Thanks in advance.

2

There are a lot of threads regarding this problem, search for NR==FNR with our internal Google search engine in the top right corner.

Regards

3

actually you need to sort your file first

sort file1 > file1.tmp
sort file2 > file2.tmp
join -o2.1 2.2 2.3 1.2 file1.tmp file2.tmp
rm file1.tmp file2.tmp