sqlpostgresqlpgadminpostgresql-9.1postgresql-9.2

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


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.


Solution

  • 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;