How to write recursive SELECT statements

Question

I have a table of ID's of some books. The table is called prequel and has two columns: "resid" and "prequelid". If a pair of ID's is present as a row in this table it means that the "prequelid"-value is the prequel of the "resid"-value. How can I write a query that, for each book series, connects all the prequels/sequels in the series in the proper order? Example: The books in the Lord of the Rings series have the following ids: B1002, B1003, B1004. I want the row for this series to look like this (one column called series): B1002 => B1003 => B1004

Perhaps this can be done recursively? With CONCAT()? Remember I'm using Postgres and syntax might be different from MySQL or SQL Server.

Could somebody write a query that does this? The ID's have a defined domain CHAR(5). Assume that no book can have more than one prequel.

 resid | prequelid
-------+-----------
 B1003 | B1002
 B1004 | B1003
 B5002 | B5001
 B5003 | B5002
 B5004 | B5003
 B5005 | B5004
 B5006 | B5005
 B5007 | B5006
(8 rows)

In this case there would be 2 rows in the result from the query. One row with 3 books (the Lord of the Rings series) and another row with 7 books (the Harry Potter series)

Answer 1

I think this does what you want:

with recursive cte as
      (select b.prequelid, b.resid, b.prequelid || '=>' || b.resid as path, 1 as lev
       from books b
       where not exists (select 1 from books b2 where b2.resid = b.prequelid)
       union all
       select cte.prequelid, b.resid, path || '=>' || b.resid, lev + 1
       from cte join
            books b
            on cte.resid = b.prequelid
       where lev < 5
      )
select distinct on (prequelid) *
from cte
order by prequelid, lev desc;

Here is a db<>fiddle.