pl sql query recuresive looping

Question

i have only one table "tbl_test"

Which have table filed given below

tbl_test table
trx_id | proj_num  | parent_num|
1      | 14        | 0         |
2      | 14        | 1         |
3      | 14        | 2         |
4      | 14        | 0         |
5      | 14        | 3         |
6      | 15        | 0         |

Result i want is : when trx_id value 5 is fetched

it's a parent child relationship. so,

trx_id -> parent_num
5      -> 3
3      -> 2
2      -> 1

That means output value:

3
2
1

Getting all parent chain

Query i used :

SELECT *  FROM ( 
    WITH RECURSIVE tree_data(project_num, task_num, parent_task_num) AS( 
    SELECT project_num, task_num, parent_task_num 
          FROM tb_task 
          WHERE project_num = 14 and task_num = 5
             UNION ALL 
             SELECT child.project_num, child.task_num, child.parent_task_num
              FROM tree_data parent Join tb_task child 
               ON parent.task_num = child.task_num AND parent.task_num = child.parent_task_num 
             ) 
          SELECT project_num, task_num, parent_task_num 
           FROM tree_data 
              ) AS tree_list ;

Can anybody help me ?

Mysql doesn't support WITH, so better try this one, http://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql — Balamurugan G, Mar 14 '13 at 12:29
@face.peck This is tagged as postgresql. Where do you get mysql from? — Chris Travers, Mar 14 '13 at 12:33

score 1 · Answer 1 · answered Mar 14 '13 at 12:32

1

There's no need to do this with pl/pgsql. You can do it straight in SQL. Consider:

WITH RECURSIVE my_tree AS (
     SELECT trx_id as id, parent_id as parent, trx_id::text as path, 1 as level
       FROM tbl_test
      WHERE trx_id = 5 -- start value
  UNION ALL
     SELECT t.trx_id, t.parent_id, p.path || ',' || t.trx_id::text, p.level + 1
       FROM my_tree p
       JOIN tbl_text t ON t.trx_id = p.parent
)
select * from my_tree;

answered Mar 14 '13 at 12:32

Chris Travers

25,424
6
65
182

this query go on running .Not stopping and throwing output – Arunraj Chandran Mar 14 '13 at 12:56
Is it possible you have a hierarchical loop? If you want to see what it is returning add to the second SELECT in my_tree, `WHERE level < 5` and it will only return the first 4 iterations. This can be useful for debugging in this case. – Chris Travers Mar 14 '13 at 13:28
Also noting, if you are *expecting* loops (i.e. this is a graph instead of a tree), you have to do some extra things to filter them out, – Chris Travers Mar 14 '13 at 13:33

score 0 · Answer 2 · edited Mar 14 '13 at 12:52

If you are using PostgresSQL, try using a WITH clause:

WITH regional_sales AS (    
        SELECT region, SUM(amount) AS total_sales
         FROM orders
         GROUP BY region
         ), top_regions AS (
         SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
        )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

pl sql query recuresive looping

2 Answers2