Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

join command issue,thanks!!!

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting join command issue,thanks!!!
# 1  
Old 01-02-2008
join command issue,thanks!!!
Hi,

This is the second time for me to ask question about join command, i really feel sorry and shame. But it is very difficult to me.

Please kindly help on how to get my desired result(i give it below), in additon to tell me how, please also kindly tell me why.

Thanks in advanced.

input:

Code:
file1:
47|1
48|1
216|1
219|1
483|1
484|1

file2:
47|Oct|3ZL1998
48|Dec|3BC1977
216|sept|3ZL1998
219|dec|2CC1999
483|may|5PA1998
484|nov|7PL1996

code:
Code:
join -t"|" -j1 1 -j2 1 file1 file2

output:
Code:
47|1|Oct|3ZL1998
48|1|Dec|3BC1977
483|1|may|5PA1998
484|1|nov|7PL1996

What i expected is:
Code:
47|1|Oct|3ZL1998
48|1|Dec|3BC1977
216|1|sept|3ZL1998
219|1|dec|2CC1999
483|1|may|5PA1998
484|1|nov|7PL1996

# 2  
Old 01-02-2008
Tools
From join manpage:

Quote:
file1 and file2 must be sorted in increasing collating
sequence as determined by LC_COLLATE on the fields on which
they are to be joined, normally the first in each line (see
sort(1)).
Sort it and it works:
Code:
$ cat f1
47|1
48|1
216|1
219|1
483|1
484|1
$ cat f2
47|Oct|3ZL1998
48|Dec|3BC1977
216|sept|3ZL1998
219|dec|2CC1999
483|may|5PA1998
484|nov|7PL1996

$ sort -t"|" -k1,1 f1 > f1.sort
$ sort -t"|" -k1,1 f2 > f2.sort

$ cat f1.sort
216|1
219|1
47|1
48|1
483|1
484|1
$ cat f2.sort
216|sept|3ZL1998
219|dec|2CC1999
47|Oct|3ZL1998
48|Dec|3BC1977
483|may|5PA1998
484|nov|7PL1996

$ join -t"|" -j 1 f1.sort f2.sort
216|1|sept|3ZL1998
219|1|dec|2CC1999
47|1|Oct|3ZL1998
48|1|Dec|3BC1977
483|1|may|5PA1998
484|1|nov|7PL1996

HTH
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

How to use the the join command to join multiple files by a common column

Hi, I have 20 tab delimited text files that have a common column (column 1). The files are named GSM1.txt through GSM20.txt. Each file has 3 columns (2 other columns in addition to the first common column). I want to write a script to join the files by the first common column so that in the... (5 Replies)
Discussion started by: evelibertine
5 Replies

2. UNIX for Dummies Questions & Answers

issue when using join

dear friends, attached are two sorted files. I want to merge them using key column: Column #2 in first file and column #1 in the second file. I want to show all columns in the merged output file. my code used is: join -a1 -a2 -1 2 -2 1 -o 1.1 1.2 1.3 2.1 uncontrols2_by_ilmn_gene.txt... (4 Replies)
Discussion started by: forevertl
4 Replies

3. UNIX for Dummies Questions & Answers

how to join two files using "Join" command with one common field in this problem?

file1: Toronto:12439755:1076359:July 1, 1867:6 Quebec City:7560592:1542056:July 1, 1867:5 Halifax:938134:55284:July 1, 1867:4 Fredericton:751400:72908:July 1, 1867:3 Winnipeg:1170300:647797:July 15, 1870:7 Victoria:4168123:944735:July 20, 1871:10 Charlottetown:137900:5660:July 1, 1873:2... (2 Replies)
Discussion started by: mindfreak
2 Replies

4. Programming

LEFT JOIN issue in Mysql

I have a data table as follows: mysql> select * from validations where source = "a03"; +------------+-------+--------+ | date | price | source | +------------+-------+--------+ | 2001-01-03 | 80 | a03 | | 2001-01-04 | 82 | a03 | | 2001-01-05 | 84 | a03 | | 2001-01-06... (2 Replies)
Discussion started by: figaro
2 Replies

5. Shell Programming and Scripting

awk command for simple join command but based on 2 columns

input1 a_a a/a 10 100 a1 a_a 20 200 b1 b_b 30 300 input2 a_a a/a xxx yyy a1 a1 lll ppp b1 b_b kkk ooo output a_a a/a 10 100 xxx yyy (2 Replies)
Discussion started by: ruby_sgp
2 Replies

6. Programming

sql,multiple join,outer join issue

example sql: select a.a1,b.b1,c.c1,d.d1,e.e1 from a left outer join b on a.x=b.x left outer join c on b.y=c.y left outer join d on d.z=a.z inner join a.t=e.t I know how single outer or inner join works in sql. But I don't really understand when there are multiple of them. can... (0 Replies)
Discussion started by: robbiezr
0 Replies

7. Shell Programming and Scripting

join command

All, I have 3 files (tab seperated): Note: Please treat dash (-) as empty value in the file, I have added it to make input easily readable file1: (2 cols) A 1 B 2 C 3 file2: (4 cols - col4 has empty values for 4th column except 2nd row) A 1 5 - B 2 6 Y C 3 7 - D 4 8 - file3... (1 Reply)
Discussion started by: bgmmk
1 Replies

8. Shell Programming and Scripting

Help with using join command

Dear, Im using Join command but I have the below problem. first file1 was BSS01 101 BSC_Olymbia_SDH 02/06/2008 22:59:08 BSS01 107 Swafieh 14/06/2008 11:11:39 BSS01 147 Kalha_Rep 11/06/2008 19:13:39 BSS01 147... (4 Replies)
Discussion started by: yahyaaa
4 Replies

9. Shell Programming and Scripting

join (pls help on join command)

Hi, I am a new learner of join command. Some result really make me confused. Please kindly help me. input: file1: LEO oracle engineer 210375 P.Jones Office Runner ID897 L.Clip Personl Chief ID982 S.Round UNIX admin ID6 file2: Dept2C ID897 6 years Dept5Z ID982 1 year Dept3S ID6 2... (1 Reply)
Discussion started by: summer_cherry
1 Replies

10. Shell Programming and Scripting

join command

Hi, I'm using the join command and it appears to discard certain fields. Here are the two files i'm comparing: File1: 1 a 2 b 3 c 4 d 99 f 101 g 999 i 200 j File 2: 1 e 2 f 3 g 4 h 99 h (22 Replies)
Discussion started by: penfold
22 Replies
Login or Register to Ask a Question