Implementing some kind of simple loop in SQLite

Question

I have a table which has these columns: id, text, parentid. And when a row is a root item (doesn't have any parent item), then parentid = 0.

What I want to do, is find text of the first root (root of root of ... root of item) of a specific item.

Here's an example:

SELECT parentid FROM cat WHERE id = 1234 --returns 1120
SELECT parentid FROM cat WHERE id = 1120 --returns 1011
SELECT parentid FROM cat WHERE id = 1011 --returns 0. So this the first root.
SELECT text FROM cat WHERE id = 1011 --returns what I want.

I know it's easily possible with Loops, but I'm using sqlite which doesn't support loops.

So, the question is, is there any way to implement this in sqlite without using any other scripts?

@Blorgbeard I can't understand how it works. Can you please explain it a bit more, and post it as an answer? — Mahdi Ghiasi
– Mahdi Ghiasi, Commented Jul 2, 2014 at 22:31
Oops, something was wrong in the example. (Last query, number should be 1101 not 1120) I just edited it. — Mahdi Ghiasi
– Mahdi Ghiasi, Commented Jul 2, 2014 at 22:36
I don't have time to spend on that right now, but maybe someone else will. There are copious examples on that page, you could probably figure it out. — Blorgbeard
– Blorgbeard, Commented Jul 2, 2014 at 22:37

cha · Accepted Answer · 2014-07-03 04:00:53Z

2

This recursive CTE will give you the desired result. Please note that the CTEs are available only in the latest versions of SQLite starting from version 3.8.3

;with cte as (
  select id, parentid, text, 1 level 
  from t where id = 1234
  UNION all
  select t.id, t.parentid, t.text, cte.level + 1 
  from cte inner join t on cte.parentid = t.id 
  where cte.parentid <> 0)
select * from cte where parentid = 0

answered Jul 3, 2014 at 4:00

cha

10.4k1 gold badge20 silver badges27 bronze badges

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

1 Comment

Mahdi Ghiasi Over a year ago

Thanks for mentioning the necessary SQLite version. I had to update my SQLite libraries.

Collectives™ on Stack Overflow

Implementing some kind of simple loop in SQLite

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related