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?

Answer 1

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.