jeudi 29 janvier 2015

Recursive CTE query without top level result in SQLite

I'm currently working on a recursive query on an Android SQLite database. I have a table containing assets, which can form a hierarchy by referring to parents. For example:



AssetId | ParentAssetId 1--2--5
----------------------- | |
1 | NULL | |--6--8
2 | 1 | | |
3 | 1 | | |--9
4 | 1 | |
5 | 2 | |--7
6 | 2 |
7 | 2 |--3
8 | 6 |
9 | 6 |--4--10
10 | 4


I need to find all of the descendents of a given start point, but not including the start point. For example:



1 = 2,3,4,5,6,7,8,9,10
2 = 5,6,7,8,9
6 = 8,9


I managed to get this working using the example from the SQLite page:


SQLite WITH page



WITH RECURSIVE
Child(AssetId) AS (
VALUES (1)
UNION
SELECT Assets.AssetId FROM Assets, Child
WHERE Assets.ParentAssetID = Child.AssetId)
SELECT AssetId FROM Child WHERE AssetId != 1


This works, but I'm not happy regarding the final WHERE clause to filter out the original item. Is there some other way to start the cascade without including the original item?


Aucun commentaire:

Enregistrer un commentaire