SQLite recursive query

Question

SQLite recursive query

Navigation

#1 by (1 votes)

1

I'm trying to query 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
    );

Vertices 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:

Iwouldliketogetthefollowingoutput:

I've made the following query:

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 gotten the following error:

Error: circular reference: vertices

I tried to use WITH RECURSIVE , but without success too.

sql sqlite query

asked by anonymous 29.05.2018 / 04:19

1 answer

Geany Text Editor Form Visual Studio Covering the Windows Taskbar or Behind the Taskbar

score 1 · Accepted Answer

A different possibility to solve the problem is to use a subquery within the SELECT clause, which looks for the child author related to the parent author through the vertices table (selected in the main query) / p>

The query looks like this:

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 (parent) found in the vertices table, a new query is made in the sugestoes table by id_node_filho , returning the autor (child) field that will be displayed next to the result main query.

More details about subqueries: SQLite: Subqueries