FarIDM FarIDM - 7 months ago 11
SQL Question

Hierarchical query to return parent activity and dependent activities

I have to Create a function that take activity_id as an argument.

Function should return activity direct predecessor and indirect predecessors and their relationship and with all the sub activities that exists in tree hierarchy.
All the sub activities with predecessors in a hierarchical way.

My table structure is as follows:

Predecessor

CREATE TABLE public.predecessor
(
predecessor_id integer NOT NULL ,
current_activity_id integer,
dependent_activity_id integer,
createdby integer,
created_at timestamp without time zone NOT NULL DEFAULT now()
)


Activities

CREATE TABLE public.activities
(
activity_id integer NOT NULL,
parent_id integer,
name text,
type_id integer NOT NULL DEFAULT 11
)


I know its a very brief representation but i will appreciate quick answers or guidelines to formulate a query.

Sample Data in Tables is as follows

Activities Data

activity_id|parent_id
8000| |
8100|8000|
8200|8000|
8120|8100|
8130|8100|
8140|8100|
8220|8200|
8230|8200|
8240|8200|
8241|8240|
8242|8240|
8243|8240|
7000| |
7100|7000|
7200|7000|
7300|7000|
7210|7200|
7220|7200|
6100|6000|
6200|6000|
6210|6200|
6220|6200|
6230|6200|
6240|6200|
6241|6240|
6242|6240|
6243|6240|
6244|6240|


Predecessor Data

activity_id|parent_id
8100|8120
8100|8130
8100|8140
8200|8220
8200|8230
8200|8240
7000|7100
7000|7200
7000|7300
6200|6210
6200|6220
6200|6230
6200|6240
6240|6241
6240|6242
6240|6243
6240|6244

Answer

select * from get_activity_ancdesc(8200)

act_id |parent_id     
8000   |
8200   |8000
8220   |8200
8230   |8200
8240   |8200
8241   |8240
8242   |8240
8243   |8240

select * from get_activity_anc(8200)

act_id |parent_id     
8000   |
8200   |8000

select * from get_activity_desc(8200)

act_id |parent_id|level
8220   |8200     |0
8230   |8200     |0
8240   |8200     |0
8241   |8240     |1
8242   |8240     |1
8243   |8240     |1