How To Make Recursive Query In Sqlite?

if my data structure is like this parentA -------parentAA --------------parentAAA ---------------------childA if i can get 'childA.name' . how can i know all the parent name till

Solution 1:

In this SQLite Release 3.8.3 On 2014-02-03 has been added support for CTEs. Here is documentation WITH clause Example:

WITHRECURSIVE
cnt(x) AS (
 SELECT1UNIONALLSELECT x+1FROM cnt
  LIMIT 1000000
)
SELECT x FROM cnt;

Solution 2:

I have a table called project with a column named rates. The rates column is a string that holds a JSON array. To split this string into rows that I can use in an IN statement to get rows from the related table, I use this for the IN part

WITH
 split(s, p) AS (
 SELECT substr(printf("%s%s", ss, ","), instr(ss, ",")+1), trim(substr(ss, 0, instr(ss, ","))) from ( select replace(replace(rates,"[",""), "]","") ss from project where rowid =1)
 UNIONALLSELECT substr(s, instr(s, ",")+1), trim(substr(s, 0, instr(s, ","))) FROM split
 where p!=""
 )
 select p from split where p!=""

Solution 3:

You can use nested set model. Nested sets have big advantage that they can be implemented in most SQL engines using simple, non-recursive SQL queries.

Solution 4:

SQLite doesn't support recursive CTEs (or CTEs at all for that matter),

there is no WITH in SQLite. Since you don't know how deep it goes, you can't use the standard JOIN trick to fake the recursive CTE. You have to do it the hard way and implement the recursion in your client code:

• Grab the initial row and the sub-part IDs.
• Grab the rows and sub-part IDs for the sub-parts.
• Repeat until nothing comes back.