How to Perform Recursive Queries in SQLite3 to Find All Subparts of a Given SuperPart?

Recursive Queries in SQLite3

Question:

In SQLite3, how can you perform a recursive query to find all pairs of a given SuperPart with its subParts?

Answer:

In SQLite versions 3.8.3 and higher, recursive queries are supported using Common Table Expressions (CTEs) with the WITH syntax:

WITH RECURSIVE Subparts AS (
  SELECT Part, SuperPart FROM Part
  UNION ALL
  SELECT p.Part, p.SuperPart FROM Part p JOIN Subparts s ON p.SuperPart = s.Part
)
SELECT * FROM Subparts;

For versions prior to 3.8.3, SQLite did not support CTEs, including recursive CTEs. Therefore, the recursion must be implemented manually in the client code, as follows:

1. Fetch the initial row and sub-part IDs.
2. Fetch the rows and sub-part IDs for the sub-parts.
3. Repeat steps 1 and 2 until no more rows are returned.

The above is the detailed content of How to Perform Recursive Queries in SQLite3 to Find All Subparts of a Given SuperPart?. For more information, please follow other related articles on the PHP Chinese website!