Android SQLite database recursive query

Question

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

@wqrahd is there any replacement for above query in android? — Swapnil, Feb 28 '14 at 05:27
Visit the [SQLite site](http://sqlite.org/lang_select.html)... and try a litter harder. Is it really so difficult to google 'SQLite syntax'??? — Merlevede, Feb 28 '14 at 05:28
no...still searching. i guess you need to get first cursor object of this : select categoryID, name, parentID from categories and then get data from this cursor and use it to join the other table. — Waqar Ahmed, Feb 28 '14 at 05:55
@wqrahd even i had this in mind but i think this wont be a feasible method why not pull data just in one query like MYSQL.So is there any other way to get it work — Swapnil, Feb 28 '14 at 06:01
i 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, Feb 28 '14 at 06:04
"from categories join" this is in your code. BUT in error log its showing as "FROM category join" — Padma Kumar, Feb 28 '14 at 06:33
@CL. actually i used this query in mysql but i required this query in my sqlite android to get huge data with single query but it give me error mentioned in logcat — Swapnil, Feb 28 '14 at 09:48
I do not know MySQL, so I do not know what this query does, so I cannot help you unless you tell me. — CL., Feb 28 '14 at 10:41
@CL. check this link http://stackoverflow.com/questions/21926832/joins-in-mysqli-for-fetching-multiple-tables-data — Swapnil, Feb 28 '14 at 10:43
@CL. this is link http://stackoverflow.com/questions/22039322/fetching-table-data-with-single-query — Swapnil, Feb 28 '14 at 10:49

CL. · Answer 1 · 2016-01-05T20:47:37.753

4

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.

edited Jan 05 '16 at 20:47

answered Feb 28 '14 at 11:04

CL.

165,803
15
203
239

1

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 – Parth Doshi Jan 05 '16 at 16:55

Android SQLite database recursive query

1 Answers1