join function and -o and -a

Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers join function and -o and -a
# 1  
Old 08-07-2002
join function and -o and -a

I'm completing a join where I specify the outfile format....such as:

join -t \| -j1 1 -j2 1 -o 1.1 1.2 1.3 2.4 1.5 1.6 1.7 $OUTFILE TMP_INFILE > NEW_OUTFILE

Now this works great. Now what I also want is to include any unmatched rows from the first file...and I use the -a flag for this.

What I get however is a null value in the field that is specifed to come from the second file for matches. You'll see in the -o I have 1.1 1.2 .....and 2.4 - so I want the fourth field to come from the second file - but obviously only when there is a match.

I'm getting no return in the 4th column now - it seems to just be going to the 2nd file to find this field even for the unmatched records....

How can I make the -a just insert the whole record when there is no match - and ignore the -o?

I realise that there are hack ways of doing this with sed and cut and others - but I want to understand joins as I'm just starting to use them.
# 2  
Old 08-08-2002
I haven't had much love on this issue....but I have a work around - if anyone is interested.

Well I have figured out one solution....but am still curious why the join function should work this way. Would seem to be a flaw?

I got around this by using the same field reference in both files then piping this through awk, and where 2.4 was not valid using 1.4 - or when 2.4 was valid using that i.e.

join -t\| -j1 1 -j2 1 -a 1 -o 1.1 1.2 1.3 1.4 2.4 1.5 1.6 1.7 $OUTFILE TMP_INFILE |
awk -F\| '{
if ($5 ~ /^[0-9][0-9][0-9][0-9]$/) {
x = $5
} else {
x = $4

printf("%s|%s|%s|%s|%s|%s|%s\n", $1, $2, $3, x, $6, $7, $8);
}' > replaced
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. Shell Programming and Scripting

Function - Make your function return an exit status

Hi All, Good Day, seeking for your assistance on how to not perform my 2nd, 3rd,4th etc.. function if my 1st function is in else condition. #Body function1() { if then echo "exist" else echo "not exist" } #if not exist in function1 my all other function will not proceed.... (4 Replies)
Discussion started by: meister29
4 Replies

3. Shell Programming and Scripting

Problem of Perl's "join" function

$ perl -e '@f=("aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa","1","911"); print join("\t",@f)."\n";' aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa ... (5 Replies)
Discussion started by: carloszhang
5 Replies

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

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

6. Shell Programming and Scripting

Join Function in PERL

Hi, Can any one please let me know, how to join the lines in a file, but based one a condition. There is a file, where few lines start with a date stamp. and few do not. I wanted to join the lines till I find a date stamp. If found date its should in a newline. Please help me. ... (5 Replies)
Discussion started by: thankful123
5 Replies

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

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

9. Shell Programming and Scripting

Passing global variable to a function which is called by another function

Hi , I have three funcions f1, f2 and f3 . f1 calls f2 and f2 calls f3 . I have a global variable "period" which i want to pass to f3 . Can i pass the variable directly in the definition of f3 ? Pls help . sars (4 Replies)
Discussion started by: sars
4 Replies

10. 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
Login or Register to Ask a Question