How to use a recursive query as a subquery?

Question

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

I could not figure out how to do this. I think I can do this using the cursor, preparing the sql statement at runtime, and then use EXEC (mySQLstatement) to run it on every FETCH NEXT cursor.

In any case, this is not a very good approach.

This is a problem (of course, it is simplified here, and I leave only the necessary columns for expression): I have a client tree (hierarchy), and for each client there are certain contacts.

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

With this request (it works), I can get all contacts for client 308 and all contacts for my sub-clients:

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 the same for ALL CUSTOMERS in one request, not only for 308. Therefore, I suggest using the cursor so that I can reuse the above operator and just use the variable instead of 308.

But can you offer a better request?

Answer 1

Just remove the filter 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.

Please note that the same customers can be returned several times.

Say a grandson will be returned at least three times: in his grandfather tree, his parent tree, and in his own tree, but each time with a different root_customer .