Thursday, June 12, 2008

Perform outer join - bash join command


#The details file, Id,Name,Age


$ cat details.txt
IDxx,MrA,43
IDtt,MrsD,35
IDkk,MrN,67
IDaa,MrC,25
IDdd,MrsQ,78


#location file, which contain location information for some Ids


$ cat location.txt
IDkk,IN
IDxx,GB
IDaa,US


Output required: join both the files details.txt and location.txt
#Id,Name,Location(If present),Age


IDaa,MrC,US,25
IDdd,MrsQ,,78
IDkk,MrN,IN,67
IDtt,MrsD,,35
IDxx,MrA,GB,43



$ sort -t "," -k1 details.txt > details.txt.srt
$ sort -t "," -k1 location.txt > location.txt.srt

$ cat details.txt.srt
IDaa,MrC,25
IDdd,MrsQ,78
IDkk,MrN,67
IDtt,MrsD,35
IDxx,MrA,43

$ cat location.txt.srt
IDaa,US
IDkk,IN
IDxx,GB


A good learn from unix.com

join(1) is a relational database operator. If your version of join conforms to POSIX.1:2004 or SUSv3 i.e.supports the -0 0 outer join operation, the following


$ join -t, -a 1 -a 2 -o 0,1.2,2.2,1.3 details.txt.srt location.txt.srt



Related Post:


bash join command
join using awk

2 comments:

lesley_gray7 said...

THANKS SO MUCH!

Biigg help for me!

gg said...

You the best!

-Thanks

© Jadu Saikia www.UNIXCL.com