android Sqlite Recursive query on 3.7.11 sqliteVersion

Question

Hello all i have to use recursive query to find hierarchy of parents of given child i used

WITH  AllCategory(x) AS ( SELECT 'C1002' UNION ALL SELECT View_Cate.parentCode  FROM View_Cate, AllCategory  WHERE View_Cate.code=AllCategory.x AND View_Cate.parentCode IS NOT NULL )
 SELECT DISTINCT * FROM AllCategory

the above query working properly but as i am using minSdkVersion 16 i am not able to use RECURSIVE so i use alternate of

SELECT t1.code AS lev1, t2.code as lev2, t3.code as lev3, t4.code as lev4,t5.code as lev4
FROM View_Cate AS t1
LEFT JOIN View_Cate AS t2 ON t2.code = t1.parentCode
LEFT JOIN View_Cate AS t3 ON t3.code = t2.parentCode
LEFT JOIN View_Cate AS t4 ON t4.code = t3.parentCode
LEFT JOIN View_Cate AS t5 ON t4.code = t4.parentCode
WHERE t1.code = 'C1003';

but problem in above is i need to know how much level of parents available. in in my context no of parents is not defined. can anybody have other good solution for this problem please help me

CL. · Accepted Answer · 2017-06-20 13:20:24Z

1

Recursive CTEs were added to SQLite because there is no other mechanism to do this with a single query.

You have to implement the recursion algorithm manually, i.e., repeatedly read the parent codes for previous results until you get no more results.

answered Jun 20, 2017 at 13:20

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

android Sqlite Recursive query on 3.7.11 sqliteVersion

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related