Avoid joining to the same table multiple times

Question

I have two tables:

CREATE TABLE one (
  id int4 primary key,
  p_id int4,
  k_id int4,
  c_id int4
);

CREATE TABLE two(
 id int4 primary key,
 p_id int4,
 k_id int4,
 t_id int4,
 pos int4
);

Table one: is a join table, I'm using it in this report only because of the c_id and to limit the number of row.

Table two: contains my data, a p_id, k_id and t_id can have many pos, so I'm grouping them and using min(), to get the min pos. I've tried some ways to get all my data for a k_id and p_id here is a way (with this solution I was not able to order by pos), so I end up doing this (see fiddle):

SELECT
  o.p_id,
  o.k_id,
  min(t.pos) as t_pos,
  min(t1.pos) as t1_pos
FROM one o
  LEFT JOIN two t ON t.p_id = o.p_id 
    AND t.k_id = o.k_id
    AND t.t_id = 1
  LEFT JOIN two t1 ON t1.p_id = o.p_id 
    AND t1.k_id = o.k_id
    AND t1.t_id = 2
WHERE o.p_id = 1 AND o.c_id = 1
GROUP BY 1, 2
LIMIT 1

This solution is 2-3 times slower then @Erwin Brandstetter's solution from the other post, but sorting t_pos, t1_pos is possible and also getting all data where type_id = 1 and pos = 1 for example.

Is there a better way of achieving the same output and improve the speed and be able to sort my data (t_pos and t1_pos columns)?

Aneesh Mon N · Accepted Answer · 2015-07-17T11:17:56.900

Try how good this works for you

Converted left join to one inner join for table Two for both t_id's. And on computing min, using case statement to split to two columns based on the value t_id. What we save here is scanning the table two time and an inner join compared to left join

SELECT
    o.p_id,
    o.k_id,
    MIN(case when t.t_id = 1 then t.pos else null end) AS t_pos,
    MIN(case when t.t_id = 2 then t.pos else null end) AS t1_pos
FROM
    one o
INNER JOIN two T ON t.p_id = o.p_id
AND t.k_id = o.k_id
AND t.t_id =any ( 1,2)
WHERE
    o.p_id = 1
AND o.c_id = 1
GROUP BY
    1,
    2
LIMIT 1

Avoid joining to the same table multiple times

1 Answers1