grepping of common lines between two file based on specific columns values in case of multi matching

Question

I want to match column3 of file1 & column2 of file2 , after that i want to print all column of matching lines in a single line, i know how to do it but the problem is that if there are not unique vales in column2 of file2 then output will be last matching line of file2. but i want that (conflicting) multi matching lines in seperate file & only matching unique line in output file.
example:-
I have two file like these:

file1

abc ram_1 ram1  
abc ram[0] ram0  
bcd raghu_reg_9 raghu9  
cde tanu/8 tanu8 

file2

1 ram1  
2 thakur56  
3 ram0  
4 ram1  
5 ram2  
6 raghu9  
7 raghu  

I tried awk as:

awk 'FNR==NR{a[$2]=$0;next} { if ($3 in a){print a[$3],$1,$2}}' file2 file1  

This gives output as:

4 ram1 abc ram_1  
3 ram0 abc ram[0]  
6 raghu9 bcd raghu_reg_9 

But i want output1 as:

3 ram0 abc ram[0]  
6 raghu9 bcd raghu_reg_9  

And in output2 as:

1 ram1 abc ram_1  
4 ram1 abc ram_1  

because there are two entries for ram1 in column2 of file2 & when we match ram1 of column3 of file1 to the ram1 of column2 of file2 then this will match twice & will give 2nd ram1 as output, but i want that if this type of condition arise then these conflicting lines should go to seperate file so that i can manually decide which one should i choose.

Answer 1

You can use join(1) to combine the files into one row per matching key:

$ join -1 3 -2 2 -o 2.1,2.2,1.1,1.2 <(sort -k3,3 file1) <(sort -k2,2 file2)
6 raghu9 bcd raghu_reg_9
3 ram0 abc ram[0]
1 ram1 abc ram_1
4 ram1 abc ram_1

What this is doing is joining the two files on field 3 of file 1 (-1 3) and field 2 of file 2 (-2 2) outputting fields 1&2 of file 2 followed by fields 1&2 of file 1 (-o 2.1,2.2,1.1,1.2).

Join requires that each input file be sorted on the join fields, so <(sort -k3,3 file1) and <(sort -k2,2 file2) uses bash(1) "process substitution" to do concurrent input pipelines and feed it to the join command.

With that output, you can use uniq(1) to extract the unique and duplicate lines. Call the above command joinit, and you can do this:

$ joinit | uniq -u -f 1
6 raghu9 bcd raghu_reg_9
3 ram0 abc ram[0]

This prints out the unique lines (-u) after skipping the first field (-f 1).

$ joinit | uniq -D -f 1
1 ram1 abc ram_1
4 ram1 abc ram_1

This prints out all the duplicated lines (-D) after skipping the first field (-f 1).

To tie that all together and put your output in output1 and output2, you can use tee(1) to feed the joinit pipeline through two separate filters:

$ join -1 3 -2 2 -o 2.1,2.2,1.1,1.2 <(sort -k3,3 file1) <(sort -k2,2 file2) \
  | tee >(uniq -u -f 1 > output1) | uniq -D -f 1 > output2

Again, this takes advantage of bash(1) "process substitution" to have concurrent output pipelines feeding each to a different uniq command.