How can I create a "recursive sql"

Question

I want to make a link "

For example, I have 5 messages (id: "1", id: "2", id: "3", id: "4", id: "5")

and they have a sequence

{id: "1", nextId: "2"},
{id: "2", nextId: "4"},
{id: "3", nextId: "0"},
{id: "4", nextId: "3"},
{id: "5", nextId: "0"},

when I search with "1", I got the result: {id: "1"}, {id: "2"}, {id: "4"}, {id: "3"} when searching from "5" I got the result: {id: "5"}

How to find everything beginning with {id: "1"} in ANSI SQL?

select s.id, s.nextId from sample s join sample ns on ns.id = s.nextId 

He first makes node everything.

I want to run "{some id}" and I want to use "limit 10"

help me!

Answer 1

I don't have HSQLDB, but something like this should do this:

 WITH RECURSIVE chain(seq, me, next) AS ( VALUES(0, CAST(null AS int), 1) -- start UNION ALL SELECT seq + 1, id, nextId FROM sample, chain WHERE id = next ) SELECT * FROM chain WHERE seq > 0; 

Answer 2

 create table links (id integer, nextid integer); insert into links values (1, 2), (2, 4), (3, 0), (4, 3), (5, 0); commit; with recursive link_tree as ( select id, nextid from links where id = 1 -- change this to change your starting node union all select c.id, c.nextid from links c join link_tree p on p.nextid = c.id ) select * from link_tree; 

This is ANSI SQL and runs on HSQLDB, PostgreSQL, H2, Firebird, DB2, Microsoft SQL Server, Oracle 11.2 and several other machines - just not on MySQL (which does not support any of the modern SQL functions that are state-of-the-art today )

Answer 3

it works on sql server, maybe it will help you in HSQLDB

in your example, if you report 1, it will return

 2->4->3->0 

it's up to you if you want to add 1 at the beginning or maybe remove 0 from the end

 CREATE table test_sequence( id int, next_id int ) insert into test_sequence VALUES(1,2) insert into test_sequence VALUES(2,4) insert into test_sequence VALUES(3,0) insert into test_sequence VALUES(4,3) insert into test_sequence VALUES(5,0) alter function selectSequence(@id int) returns varchar(max) begin declare @next varchar(max) select @next=next_id from test_sequence WHERE id =@id if (@next != '') begin return @next +'->'+ dbo.selectSequence(@next) end else begin select @next='' end return @next end select dbo.selectSequence(1) 

Answer 4

The problem with recursion is clearly demonstrated by other answers - the implementation is incompatible between RDBMS providers.

Alternatively, you can use the "nested set" , which avoids recursion in general and should be easily built in the SQL agnostic platform.