CODEWITHSUNDEEP
androidsqliteandroid-sqliterecursive-query
Swapnil
Swapnil

Reputation: 654

Android SQLite database recursive query

select @pv:=categoryID as 'categoryID', name, parentID from categories
join
(select @pv:=4) tmp
where parentID = @pv

above query working on MYSQL but its not working on my android mobile SQLite database. Is there any other solution available ?enter image description here

Upvotes: 1

Views: 1790

Answers (1)

CL.
CL.

Reputation: 180080

Instead of some vendor-specific syntax, SQLite uses the common table expressions defined in the SQL standard for recursive queries:

WITH RECURSIVE subtree
AS (SELECT categoryID, name, parentID
    FROM categories
    WHERE categoryID = 4
    UNION ALL
    SELECT c.categoryID, c.name, c.parentID
    FROM categories AS c
    JOIN subtree ON c.parentID = subtree.categoryID)
SELECT *
FROM subtree

However, CTEs are available only in SQLite 3.8.3 or later, which is available only in Android 5.0 or later. In earlier Android versions, you cannot use recursive queries and have to fetch the data of each level separately.

Upvotes: 4

Related Questions