join (pls help on join command)


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting join (pls help on join command)
# 1  
Old 12-31-2007
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:
Code:
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 years
GEDA 210375 1 year

What i exactly want is:
Code:
ID897 P.Jones Office Runner Dept2C 6 years
ID982 L.Clip Personl Chief Dept5Z 1 year
ID6 S.Round UNIX admin Dept3S 2 years
210375 LEO oracle engineer GEDA 1 year

Follow is the code and result according to my example. Could anybody explain why the result is not correct. It seems my soaris can only output first two lines(since if i split the input file to four groups, and each group contain only one line, it works ok.)

Thanks in advanced!

Code:
code1: join -j1 4 -j2 2 file1 file2
result1:
ID897 P.Jones Office Runner Dept2C 6 years
ID982 L.Clip Personl Chief Dept5Z 1 year

code2:join -a1 -j1 4 -j2 2 file1 file2
reuslt2:
210375 LEO oracle engineer
ID897 P.Jones Office Runner Dept2C 6 years
ID982 L.Clip Personl Chief Dept5Z 1 year

code3:
join -a2 -j1 4 -j2 2 file1 file2
reustl3:
ID897 P.Jones Office Runner Dept2C 6 years
ID982 L.Clip Personl Chief Dept5Z 1 year
ID6 Dept3S 2 years
210375 GEDA 1 year

# 2  
Old 12-31-2007
A quick fix :-) Hope this works

$ cat fil1
LEO oracle engineer 210375
P.Jones Office Runner ID897
L.Clip Personl Chief ID982
S.Round UNIX admin ID6

$ cat fil2
Dept2C ID897 6 years
Dept5Z ID982 1 year
Dept3S ID6 2 years
GEDA 210375 1 year

$ sort +3 -4 fil1 > fil1.tmp
$ sort +1 -2 fil2 > fil2.tmp

$ join -1 4 -2 2 fil1.tmp fil2.tmp
210375 LEO oracle engineer GEDA 1 year
ID6 S.Round UNIX admin Dept3S 2 years
ID897 P.Jones Office Runner Dept2C 6 years
ID982 L.Clip Personl Chief Dept5Z 1 year
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Join, merge, fill NULL the void columns of multiples files like sql "LEFT JOIN" by using awk

Hello, This post is already here but want to do this with another way Merge multiples files with multiples duplicates keys by filling "NULL" the void columns for anothers joinning files file1.csv: 1|abc 1|def 2|ghi 2|jkl 3|mno 3|pqr file2.csv: 1|123|jojo 1|NULL|bibi... (2 Replies)
Discussion started by: yjacknewton
2 Replies

2. UNIX for Beginners Questions & Answers

BASH join command error PLS

i've tried every variation possible and keep getting not sorted error. can anyone shed any light on how to do this? (image attached) (1 Reply)
Discussion started by: deadcick
1 Replies

3. 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

4. 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

5. UNIX for Dummies Questions & Answers

Join 2 files with multiple columns: awk/grep/join?

Hello, My apologies if this has been posted elsewhere, I have had a look at several threads but I am still confused how to use these functions. I have two files, each with 5 columns: File A: (tab-delimited) PDB CHAIN Start End Fragment 1avq A 171 176 awyfan 1avq A 172 177 wyfany 1c7k A 2 7... (3 Replies)
Discussion started by: InfoSeeker
3 Replies

6. Shell Programming and Scripting

using join command

I am given: Sara:Smith:11234:3:63498:25:40 Perry:Potter:12445:2:35664:15:40 Ann:Abbott:23323:2:54865:22:42 Maple:Myers:24223:1:63498:18:35 Harold:Hanson:35664:2:54865:10:40 Bob:Brown:40778:1:24223:15:40 Jane:Jones:41288:1:53498:24:40 Wanda:Wallace:51122:4:63498:55:40... (4 Replies)
Discussion started by: ehshi1992
4 Replies

7. 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

8. 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

9. Shell Programming and Scripting

Join command

Dear Experts, I have several (say 'm') text files, each with 'n' columns. I want to put them into a large single file with n*m columns. a 1 a 1 a 1 a 1 b 2 b 5 b 1 b 3 c 3 c 7 ... (1 Reply)
Discussion started by: Mish_99
1 Replies

10. UNIX for Dummies Questions & Answers

Join command

Hi, I am trying to join 2 files..viz f1: 12~a1 13~a2 112~a3 1112~a4 f2: 12~fa2 13~fa2 112~fa3 1112~fa4 while I join..I just get 2 o/p.. 12~a1~fa2 13~a2~fa2 (4 Replies)
Discussion started by: unisam
4 Replies
Login or Register to Ask a Question