How to use a recursive query as a subquery?

Question

I need to write a query that calls a recursive query many times.

I was not able to figure out how to do. I guess I can do this by using a cursor, preparing the sql statement at run time and then use EXEC(mySQLstatement) to run it at every cursor FETCH NEXT.

Anyway this is not the good approach.

This is the problem (of course here it is simplified and I leave only the necessary columns to express myself): I have a tree of customers (a hierarchy) and for every customer there are some contacts defined.

The CUSTOMERS table containts an ID_CUSTOMER field and an ID_PARENT_CUSTOMER field the CUSTOMER_CONTACTS table contains an ID_CUSTOMER field and an ID_CONTACT field.

With this query (it works) i am able to get all the contacts for customer 308 and all the contacts for its sub-customers:

with [CTE] as (
    select ID_CUSTOMER from CUSTOMERS c where c.ID_CUSTOMER = 308
    union all
    select c.ID_CUSTOMER from [CTE] p, CUSTOMERS c 
        where c.ID_PARENT_CUSTOMER = p.ID_CUSTOMER
)
select ID_CUSTOMER into #Customer308AndSubCustomers from [CTE]

select 308 as ParentCustomer, ID_CUSTOMER, ID_CONTACT,  from CUSTOMER_CONTACTS
WHERE ID_CUSTOMER IN (select * from #Customer308AndSubCustomers)
drop table #Customer308AndSubCustomers

But I would like to have in a single query the same for ALL THE CUSTOMERS, not only for 308. So this is why I was suggesting to use a cursor so I can reuse the above statement and just use a variable instead of 308.

But can you suggest a better query?

score 9 · Accepted Answer · answered Apr 18 '11 at 13:46

Just remove the filtering condition from the anchor part:

WITH    q AS
        (
        SELECT  ID_CUSTOMER, ID_CUSTOMER AS root_customer
        FROM    CUSTOMERS c
        UNION ALL
        SELECT  c.ID_CUSTOMER, q.root_customer
        FROM    q
        JOIN    CUSTOMERS c 
        ON      c.ID_PARENT_CUSTOMER = q.ID_CUSTOMER
        )
SELECT  *
FROM    q

root_customer will show you the root of the chain.

Note that the same customers may be returned several times.

Say, a grandchild will be return at least thrice: in its grandparent tree, its parent tree and in its own tree, but each time with a different root_customer.

yes yes yes yes yes! It is the first day for me with recursive queries, I am just not into them yet. Thanks a lot, this solved my problem! — UnDiUdin, Apr 18 '11 at 14:08

score 1 · Answer 2 · edited Nov 19 '20 at 16:13

In PostgreSQL you can write recursive query CTE as below. below query fetch all the sub category of given category with id(7)

WITH RECURSIVE category_tree(id, parent_category) AS (
   SELECT id, parent_category
   FROM category
   WHERE id = 7  -- this defines the start of the recursion
   UNION ALL
   SELECT child.id,  child.parent_category
   FROM category   child
     JOIN category_tree  parent ON parent.id = child.parent_category -- the self join to the CTE builds up the recursion
)
SELECT * FROM category_tree;

How to use a recursive query as a subquery?

2 Answers2

Linked