basic recursive query on sqlite3?

by

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:

enter image description here

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.

More Related Contents:

  1. Selecting more than one value
  2. Is it possible to insert multiple rows at a time in an SQLite database?
  3. Simplest way to do a recursive self-join?
  4. SQLite DateTime comparison
  5. SQLite Reset Primary Key Field
  6. SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)
  7. Deleting duplicate rows from sqlite database
  8. What does the SQL Standard say about usage of backtick(`)?
  9. Declare variable in SQLite and use it
  10. Does SQLite3 not support foreign key constraints?
  11. How to get a list of column names on Sqlite3 database?
  12. How do I add a foreign key to an existing SQLite table?
  13. SQL recursive query on self referencing table (Oracle)
  14. Sqlite: CURRENT_TIMESTAMP is in GMT, not the timezone of the machine
  15. sqlite alter table add MULTIPLE columns in a single statement
  16. Is there AUTO INCREMENT in SQLite?
  17. Get month from DATETIME in sqlite
  18. GROUP_CONCAT in SQLite
  19. How do I import .sql files into SQLite 3?
  20. sqlite equivalent of row_number() over ( partition by …?
  21. Android multiple databases open
  22. sqlite select with condition on date
  23. How to compare sqlite TIMESTAMP values
  24. SQlite: select into?
  25. SQLite Order By Date1530019888000
  26. IF() statement alternative in SQLite
  27. SQLite query, ‘LIKE’
  28. UPSERT *not* INSERT or REPLACE
  29. Drop column from SQLite table
  30. converting int to real in sqlite

Leave a Comment