I have two tables.
table 1
Column A | Column B |
---|---|
rgz | 1 |
dry | 1 |
vgy | 2 |
chy | 3 |
pom | 2 |
table 2
Column C | Column D |
---|---|
tuv | 10 |
tuv | 11 |
chx | 22 |
lmn | 34 |
I want the following result shown on data output of postgres PgAdmin
Column E | Column F |
---|---|
1 | tuv |
2 | chx |
3 | lmn |
I just want to be able to select distinct values from column B of table 1 and column C of table 2 and show the results as above. I do not want all possible combinations which I can get by the following query
select * from (select distinct column B from table 1) a, (select distinct column C from table 2) b
Can you please suggest a way to do this?
Thank you
G.
You seem to want only values that appear more than once. Since you won't know which side is longer you'll need a full join to pair them up:
with A as (
select columnB, row_number() over (order by min(columnB)) as rn
from Table1
group by columnB
--having count(*) > 1 /* filtering for duplicates was unnecessary */
), B as (
select columnC, row_number() over (order by min(columnC)) as rn
from Table2
group by columnC
--having count(*) > 1 /* filtering for duplicates was unnecessary */
)
select columnB, column C
from A full outer join B on B.rn = A.rn;