I am using Postgres.

I have 3 tables:


  table_1_id, table_2_id


The problem is the relationship table table_1_2 is not uniq.

I.e., table_1_2 will have the same tuples multiple times:

{1,1}, {1,1}, {1,1}, {1,1}, {1,1}, {1,2}, {1,2}, {1,2} etc.

Is there a way to join the table_1 and table_2 using table_1_2, but with only uniq result?

Currently, I will get all the repeated items as from the relationship table.


There are many ways. Here is one (should be a fast variant to retrieve all rows):

SELECT t1.*, t2.*
FROM   table1 t1
    SELECT DISTINCT table_1_id, table_2_id
    FROM   table1_2
    ) t1_2 ON t1_2.table_1_id = t1.id
JOIN   table2 t2 ON t2.id = t1_2.table_2_id;

Why not remove the duplicates permanently? And add a UNIQUE constraint ...

| improve this answer | |
  • thank you. I am planning to remove duplicates and add unique constraint. – Zhaohan Weng Mar 20 '15 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.