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 ?
-
1android does not support this notation @pv:=categoryIDWaqar Ahmed– Waqar Ahmed2014-02-28 05:27:02 +00:00Commented Feb 28, 2014 at 5:27
-
@wqrahd is there any replacement for above query in android?Swapnil– Swapnil2014-02-28 05:27:51 +00:00Commented Feb 28, 2014 at 5:27
-
Visit the SQLite site... and try a litter harder. Is it really so difficult to google 'SQLite syntax'???Merlevede– Merlevede2014-02-28 05:28:30 +00:00Commented Feb 28, 2014 at 5:28
-
@wqrahd do you find any solution?Swapnil– Swapnil2014-02-28 05:49:16 +00:00Commented Feb 28, 2014 at 5:49
-
1i guess no. becuase sqlite is different whereas mysql is different. sqlite is compact so i guess it is limited to.you have to break your work in cursor.Waqar Ahmed– Waqar Ahmed2014-02-28 06:04:07 +00:00Commented Feb 28, 2014 at 6:04
|
Show 10 more comments
1 Answer
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.
1 Comment
Parth Doshi
Apart from rewriting the query again, is there any other way bu which we can add support for recursive queries in Android API level 20 since the SQLite version 3.8.3 is present in Android 5.0 and it works fine on the same