basic recursive query on sqlite3?

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.

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

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

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.