SQL queries optimization - postgresql

Question

Am a beginner in SQL. How can I optimize this query

select * from t1 where parent_id in (select id from t1 where id= 'X')

UNION

select * from t1 where parent_id in (select id from  t1 where parent_id in (select id from t1 where id= 'X'))
UNION

select * from t1 where parent_id in (select id from t1 where parent_id in (select id from  t1 where parent_id in (select id from t1 where id= 'X')))

Possible duplicate of Find Parent Recursively using Query

Nir Alfasi
– Nir Alfasi

2019-05-02 21:25:04 +00:00
Commented May 2, 2019 at 21:25 — Nir Alfasi
– Nir Alfasi, Commented May 2, 2019 at 21:25

James · Accepted Answer · 2019-05-02 21:58:54Z

1

You can try with recursive cte:

WITH RECURSIVE cte AS (
  select * from t1 where t1.id='X'
  union all
  select t1.*
  from cte c
    join t1
      on t1.id=c.parent_id
)
select distinct * from cte

edited May 2, 2019 at 21:58

answered May 2, 2019 at 21:51

James

3,0133 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL queries optimization - postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related