How Can I Perform Recursive Queries in SQLite3 to Retrieve Hierarchical Data?

Recursive Queries in SQLite3

In SQLite3, it is possible to execute recursive queries to retrieve hierarchical data from a table that contains parent-child relationships, such as finding all subparts of a given superpart. To achieve this, you can use the WITH RECURSIVE syntax, which is supported in SQLite3 versions 3.8.3 and higher.

WITH RECURSIVE SubParts AS (
  SELECT Part, SuperPart, 1 AS Level
  FROM Part
  WHERE SuperPart = 'eZ00'
  UNION ALL
  SELECT p.Part, p.SuperPart, s.Level + 1
  FROM Part AS p
  JOIN SubParts AS s ON p.SuperPart = s.Part
)
SELECT * FROM SubParts;

The WITH RECURSIVE statement creates a temporary table called SubParts that contains the original row and a column named Level, which initializes to 1 for the parent row. The UNION ALL clause performs the recursive step by selecting rows that match the subparts of the current row and incrementing the Level by 1. The query then returns all the rows from the SubParts table.

Prior to SQLite3 3.8.3

Before SQLite3 version 3.8.3, recursive queries were not supported. To emulate recursion in client code for a multi-level hierarchy, follow these steps:

1. Retrieve the initial row and its subparts.
2. Iteratively retrieve rows and their subparts until no rows are returned.

The above is the detailed content of How Can I Perform Recursive Queries in SQLite3 to Retrieve Hierarchical Data?. For more information, please follow other related articles on the PHP Chinese website!