Problems using join for simple database lookup


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Problems using join for simple database lookup
# 1  
Old 05-19-2009
Problems using join for simple database lookup

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:

Code:
% 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:
Code:
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:
Code:
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  
Old 05-19-2009
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  
Old 05-20-2009
actually you need to sort your file first

Code:
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

Login or Register to Ask a Question

Previous Thread | Next Thread

6 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Problems host name lookup failure sendmail

Hi I have a problem in sendmail Freebsd when sending emails to yahoo as an example, I get: Sep 5 10:05:43 local50 sm-mta: STARTTLS=client, error: connect failed=-1, SSL_error=1, errno=0, retry=-1 Sep 5 10:05:43 local50 sm-mta: STARTTLS=client: 43926:error:1407742E:SSL... (3 Replies)
Discussion started by: rickyarge92
3 Replies

2. UNIX for Dummies Questions & Answers

A simple join, but nothing is working out for me

Guys, I want to join two files. You might have seen this many times. I just don't get the desired output. Searching the forum, No proper links :( Input: File1 test1 test2 test3 File2 is bad is not bad Output Needed: test1 is bad test2 is bad (4 Replies)
Discussion started by: PikK45
4 Replies

3. UNIX for Dummies Questions & Answers

Really simple cross-platform database?

I'm looking for a really simple to use lightweight database. Ideally something open-source that stores data in a (semi-) human-readable format, in case the software isn't working. Something cross-platform enough that I can use it on my Linux machine, my Mac, or stick it on a USB stick and... (2 Replies)
Discussion started by: emdan
2 Replies

4. Shell Programming and Scripting

simple join for multiple files and produce 3 outputs

sh script file1 filea fileb filec ................filez. >>output1 & output2 &output3 file1 z10 1873 1920 z_number1_E59 z10 2042 2090 z_number2_E59 Z22 2476 2560 z_number3_E59 Z22 2838 2915 z_number4_E59 z1 1873 1920 z_number1_E60 z1 ... (9 Replies)
Discussion started by: stateperl
9 Replies

5. Shell Programming and Scripting

List of IPs & database lookup

I am trying to feed a list of IP's to do lookups from a database. My script works only for the first IP but all subsequent IPs output as 'unknown'. #!/usr/bin/php -q <? $ip = file('ip.txt'); foreach ($ip as $ip_num => $ip) { echo $ip; $out=sprintf("%u", ip2long($ip)); ... (1 Reply)
Discussion started by: hazno
1 Replies

6. UNIX for Advanced & Expert Users

Clueless about how to lookup and reverse lookup IP addresses under a file!!.pls help

Write a quick shell snippet to find all of the IPV4 IP addresses in any and all of the files under /var/lib/output/*, ignoring whatever else may be in those files. Perform a reverse lookup on each, and format the output neatly, like "IP=192.168.0.1, ... (0 Replies)
Discussion started by: choco4202002
0 Replies
Login or Register to Ask a Question

Featured Tech Videos