09-19-2008
trouble with join
Hello
I've got two files; each has 6 records each. I want to join the files so I can compare the reserved space of tables on one server with the same tables on another server.
I tried the join command but, it 's not joining all of the rows. I know I'm missing something simple but, I can't see it.
Here is file1
fctenv01 CER_MCZT_ZIP_TRANS 79877 11776 10868 776 132
fctenv01 CMC_CECE_CRITERIA 150911 17692 17512 112 68
fctenv01 CMC_EBCL_EOB_DATA 106651 3168 3004 24 140
rptenv01 CER_MCZT_ZIP_TRANS 79877 11779 10877 766 142
rptenv01 CMC_CECE_CRITERIA 150923 18692 17522 122 78
rptenv01 CMC_EBCL_EOB_DATA 106652 3768 3114 44 240
Here is file2
fctenv01 CER_MCZT_ZIP_TRANS 80225 12320 10700 1496 124
fctenv01 CMC_CECE_CRITERIA 150930 17692 17512 112 68
fctenv01 CMC_EBCL_EOB_DATA 106470 3136 3000 28 108
rptenv01 CER_MCZT_ZIP_TRANS 80229 12439 12475 1596 224
rptenv01 CMC_CECE_CRITERIA 151230 18792 18722 212 78
rptenv01 CMC_EBCL_EOB_DATA 116570 3276 3123 78 128
Here is the join statement I am using
join -1 2 -2 2 $file1 $file2 | awk '{tot=$3-$4};{print $2,$1,$3,$4,tot}' | sort -1 +4rn
And here is the output
fctenv01 CMC_CECE_CRITERIA 150911 17692 133219
fctenv01 CMC_EBCL_EOB_DATA 106651 3168 103483
fctenv01 CER_MCZT_ZIP_TRANS 79877 11776 68101
rptenv01 CMC_EBCL_EOB_DATA 106652 3768 102884
rptenv01 CMC_EBCL_EOB_DATA 106652 3768 102884
The first two rptenv01 tables are missing on the output and the last rptenv01 table is listed twice.
Here is the output I would like the join to give me
fctenv01 CMC_CECE_CRITERIA 150911 17692 133219
fctenv01 CMC_EBCL_EOB_DATA 106651 3168 103483
fctenv01 CER_MCZT_ZIP_TRANS 79877 11776 68101
rptenv01 CMC_CECE_CRITERIA 150923 18692 132231
rptenv01 CMC_EBCL_EOB_DATA 106652 3768 102884
rptenv01 CER_MCZT_ZIP_TRANS 79877 11779 68098
What am I missing? Any help would be greatly appreciated!
tia
10 More Discussions You Might Find Interesting
1. UNIX Desktop Questions & Answers
Hello,
The system reboots in single user mode
what command to use to bring the system up regularly??
I get
INIT: cannot create /var/adm/utmpx
Type control -d to proceede with a normal startup
or give root passwd for system maintaince.
either way i don't have a change and ... (1 Reply)
Discussion started by: awk
1 Replies
2. What is on Your Mind?
Welcome to "The trouble with...." with your host, ZazzyBob.
Todays offering - "The trouble with letting other people host your website"
I use a certain web hosting service, who shall of course remain nameless here.
They are running PHP 4.3.10
I decide to write a script to test their PHP... (6 Replies)
Discussion started by: zazzybob
6 Replies
3. UNIX for Dummies Questions & Answers
Hi all,
having read lots of posts about SU I don't quiet understand this :
I'm doing regular backups of my database (u betta do) and therefore use su - username -c "sqlscript special data_base" in a unixscript which is even using cron. (yep!)
Now I need some other script, still with this... (4 Replies)
Discussion started by: nulnul7
4 Replies
4. Solaris
I'm not sure where to post this but it's happening on a SunOS 5.8 server so I'll try here.
I've discovered some unexpected behavior when using tr.
For example:
echo a | tr
Z
echo b | tr
a
echo a | tr
B
echo a | tr
B
echo a | tr
A (8 Replies)
Discussion started by: Mike@Work
8 Replies
5. Shell Programming and Scripting
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
6. Programming
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. UNIX for Dummies Questions & Answers
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. UNIX for Dummies Questions & Answers
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
9. UNIX for Dummies Questions & Answers
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
10. Shell Programming and Scripting
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