how to select distinct values ​​from two tables limit to one line

advertisements

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

Any ideas?


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.