Postgresql – Uniq join 3 tables with relationship table not uniq

duplicationjoin;performanceperformance-tuningpostgresql

I am using Postgres.

I have 3 tables:

table_1
  id

table_1_2
  table_1_id, table_2_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.

Best Answer

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

SELECT t1.*, t2.*
FROM   table1 t1
JOIN  (
    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 ...

Related Question