1
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

CL.
  • 173,858
  • 17
  • 217
  • 259
Swapnil
  • 654
  • 7
  • 27
  • 1
    android does not support this notation @pv:=categoryID – Waqar Ahmed Feb 28 '14 at 05:27
  • @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
  • @wqrahd do you find any solution? – Swapnil Feb 28 '14 at 05:49
  • 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
  • 1
    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
  • What are you trying to do with this query? – CL. Feb 28 '14 at 09:13
  • @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
  • What are you using `pv` for? – CL. Feb 28 '14 at 10:45
  • @CL. sorry i have given you wrong link let me correct – Swapnil Feb 28 '14 at 10:48
  • @CL. this is link http://stackoverflow.com/questions/22039322/fetching-table-data-with-single-query – Swapnil Feb 28 '14 at 10:49

1 Answers1

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.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • 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