PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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

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) bpe here

Can you please suggest a way to do this?

Thank you

G.

Tags:

2

Answers


  1. 0

    First of all, the distinct values from "column B" of "table 1" are: 1, 2, 3.
    And the distinct values from "column C" of "table 2" are: ‘tuv’, ‘chx’, ‘lmn’.
    Why do your expect 1, 2 and ‘tuv’ ?

    Login or Signup to reply.
  2. 0

    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 columnB) as rn
    from Table1
    group by columnB
    having count(*) > 1
), B as (
    select columnC, row_number() over (order by columnC) as rn
    from Table2
    group by columnC
    having count(*) > 1
)
select columnB, column C
from A full outer join B on B.rn = A.rn;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search