basic recursive query on sqlite3?

Solution 1:

If you're lucky enough to be using SQLite 3.8.3 or higher then you do have access to recursive and non-recursive CTEs using WITH:

Thanks to lunicon for letting us know about this SQLite update.

In versions prior to 3.8.3, SQLite didn't support recursive CTEs (or CTEs at all for that matter) so there was 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.

Solution 2:

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

WITH RECURSIVE
cnt(x) AS (
 SELECT 1
 UNION ALL
 SELECT x+1 FROM cnt
  LIMIT 1000000
)
SELECT x FROM cnt;

Solution 3:

This is the most basic query that I could think of, it generates a series where we start with 1,2 and keep adding 1 till we hit 20. not much useful but playing around a bit with this will help you build more complex recursive ones

The most basic series

WITH b(x,y) AS 
(
    SELECT 1,2 
    UNION ALL 
    SELECT x+ 1, y + 1 
    FROM b 
    WHERE x < 20
) SELECT * FROM b;

Prints

1|2
2|3
3|4
4|5
5|6
6|7
7|8
8|9
9|10
10|11
11|12
12|13
13|14
14|15
15|16
16|17
17|18
18|19
19|20
20|21

Here is another simple example that generates Fibonacci numbers we start with a = 0, b = 1 and then go a = b, b = a + b just like you would do in any programming language

Fibonacci Series

WITH b(x,y) AS 
(
    SELECT 0,1 
    UNION ALL 
    SELECT y, x + y 
    FROM b 
    WHERE x < 10000
) select * FROM b;

Prints

0|1
1|1
1|2
2|3
3|5
5|8
8|13
13|21
21|34
34|55
55|89
89|144
144|233
233|377
377|610
610|987
987|1597
1597|2584
2584|4181
4181|6765
6765|10946
10946|17711

Solution 4:

Based on the samples found in sqlite with documentation, the query

DROP TABLE IF EXISTS parts;
CREATE TABLE parts (part, superpart);
INSERT INTO parts VALUES("wk0Z", "wk00");
INSERT INTO parts VALUES("wk06", "wk02");
INSERT INTO parts VALUES("wk07", "wk02");
INSERT INTO parts VALUES("eZ01", "eZ00");
INSERT INTO parts VALUES("eZ02", "eZ00");
INSERT INTO parts VALUES("eZ03", "eZ01");
INSERT INTO parts VALUES("eZ04", "eZ01");

WITH RECURSIVE
  under_part(parent,part,level) AS (
     VALUES('?', 'eZ00', 0)
     UNION ALL
     SELECT parts.superpart, parts.part, under_part.level+1 
        FROM parts, under_part
     WHERE parts.superpart=under_part.part
  )
  SELECT SUBSTR('..........',1,level*3) || "(" || parent || ", " || part || ")" FROM under_part
  ;

would output

  (?, eZ00)
  ...(eZ00, eZ01)
  ...(eZ00, eZ02)
  ......(eZ01, eZ03)
  ......(eZ01, eZ04)

as "it should be" expected

the initial record of the recursive table can be replaced with

VALUES ((SELECT superpart FROM parts WHERE part='eZ00'), 'eZ00', 0)

in order to get also the parent of the initial superpart, although in this case there is no parent at all.