I have two tables with this values:
**table 1** id name value 1 A 1 2 B 1 3 F 2 4 G 3 **table 2** id name value 1 C 1 2 D 1 3 E 2
If I do an inner join of both tables by the value I´m getting this:
A C A D B C B D F E
But, the problem is that I want only distinct values from both columns like that:
A C B D F E
Another set of posible result will be:
A D B C F E
There´s no chance of name of table 1 will appear in table 2.
If one value from a column was already selected, it can´t be selected again. This example will be an error because C was already selected:
A C B C F E
In order to pair the records, you need a running number per value to link with. Use row_number() for this.
select t1.name as t1_name, t2.name as t2_name from ( select name, value, row_number() over (partition by value order by name) as rn from table1 ) t1 join ( select name, value, row_number() over (partition by value order by name) as rn from table2 ) t2 on t1.value = t2.value and t1.rn = t2.rn;
SQL fiddle: http://www.sqlfiddle.com/#!4/75de0/1.