# merging files and getting column values based on id field

bash-3.2\$ cat sample.log sample.log.1 sample.log.2
ID COL1 COL2 COL4
1  col1 col2 col4
2  c1   c2   c4
3  co1  co2  co4

ID COL3 COL1
1  col3 col1
2  c3   c1
3  co3  co1

ID COL1 COL2 COL3
1  col1 col2 col3
2  c1  c2   c3
3  co1  co2  co3


I need to write an awk script such that it gives me the values of the columns for a particular id like a select query on multiple tables in db.

give me col1 col2 and col3 fields for id 1 and should not duplicate result. meaning the result should be like

The result should be
ID COL1 COL2 COL3
1  col1 col2 col3


but not

The result should be
ID COL1 COL2 COL3 COL3
1  col1 col2 col3 col3


Even a suggestion is also good.

awk ' BEGIN { while ( (getline line < "sample.log") > 0 ) {ids[substr(line,1,index(line," ")-1)];} } { // get the column values here based on the stored id's .. } ' sample.log sample.log.1 sample.log.2


I am trying to do something like that mentioned above. I am not sure if it is a good idea.

-
You could just drop the data into SQLite and then run actual SQL queries against it. –  Ingmar Hupp Feb 23 '12 at 13:10

You can use the join command to perform this task:
join -1 1 -2 1 sample.log sample.log.1 -o 1.1,1.2,1.3,2.2

Note that the join input files must be sorted.