Recursive sql query

Question

how to write a recursive query so that each id is taken in turn, a query is executed on it?

Answer 1

Mary_d9 It's unclear what you are describing as it relates to SQL and relational databases.

Can you expand on a scenario where you would need to recursively or iteratively loop to retrieve a new ID to then use as input into another query?

Most scenarios that come to mind can be addressed with JOINS or sub queries in the WHERE clause.

If you have a scenario where JOINS or WHERE clauses won't work, please do share those details and we can explore the options.

Answer 2

Ayushman i dont understand you

Answer 3

Mary_d9 Your scenario is still unclear. I would need to see something more concrete in terms of data examples or something to better understand this use case.

I'm certain the solution is simple once I get some idea of your table structures and relationships and expected query results.

It doesn't need to be your exact tables. But the relationship need to be modeled in something tangible to work against.

At the moment, I only know you need "a loop" to execute the sub queries.

This leads me to believe you and I are referring to the notion of a sub query differently.

A sub query, as I'm referring to them, is a query within a query, like:

SELECT *
FROM Foo F
WHERE F.SomeField IN (
/* Simple Sub Query Here */
SELECT B.OtherField
FROM Bar B
WHERE B.AnotherField > @Value
)

Is this what you've been doing as well?

Answer 4

Ayushman It's cool, but this knowledge is not enough. I need to take values from with and then those values were passed one by one to another with

Answer 5

David Carroll I have several 'with' subqueries, so that the code does not turn out to be very large, I need a loop that would execute the subqueries as many times as I need. I tried to write a procedure, but with and the procedure don't work together. I get the number of executions from the subquery too. I cannot change the structure of the code, that is, change the subqueries to something else.

Answer 6

David Carroll I can't show my request. I would like to get some examples or an answer whether it is really possible to do this at all.