Select only distinct values from two tables on pgadmin without getting all combinations

Question

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.

shawnt00 · Accepted Answer · 2024-07-01 15:36:38Z

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;

Collectives™ on Stack Overflow

Select only distinct values from two tables on pgadmin without getting all combinations

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related