Query recursive Sqlite

Question

I am trying to make a query to a Sqlite database, I have the following tables:

Table suggestions:

CREATE TABLE IF NOT EXISTS sugestoes (
        id     INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        texto  VARCHAR(250),
        autor  VARCHAR(250),
        itens  VARCHAR(250),
        pontos INTEGER
    );

---

Vertical table

CREATE TABLE IF NOT EXISTS vertices (
        id            INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        id_node_pai   INTEGER,
        id_node_filho INTEGER,
        FOREIGN KEY (id_node_pai)   REFERENCES sugestoes(id),
        FOREIGN KEY (id_node_filho) REFERENCES sugestoes(id)
    );

---

Within them I have the following data:

---

I would like to get the following output:

---

I have made the following consultation:

WITH vertices AS (
    SELECT p.autor FROM sugestoes p, vertices e
        WHERE e.id_node_pai = p.id
    UNION ALL
    SELECT f.autor FROM sugestoes f
        INNER JOIN vertices d
        ON f.id = d.id_node_filho
)
    SELECT * FROM vertices;

However I have obtained the following error:

Error: circular reference: vertices

I tried to use the WITH RECURSIVE, but also unsuccessful.

Answer 1

A different possibility to solve the problem, is to use a subquery within the SELECT clause, which searches for the parent author’s related child author through the table vertices (selected in the main query).

The consultation is as follows::

SELECT p.autor AS autor_pai,
       (SELECT q.autor
          FROM sugestoes AS q
         WHERE q.id = e.id_node_filho) AS autor_filho
  FROM vertices AS e, sugestoes AS p
 WHERE e.id_node_pai = p.id

In this query, for each author (father) found in the table vertices, a new query is made in the table sugestoes for id_node_filho, returning the field autor (child) that will be displayed next to the result of the main query.

More details about subconsulta (in English): Sqlite: Subqueries