2 calls to the same table in SQLite (output different than expected)

Navigation
0

Given the following 2 tables: 

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

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)
);

I'm doing the following query: 

SELECT DISTINCT s.autor
FROM sugestoes AS s
LEFT JOIN vertices AS v
WHERE s.id <> v.id_node_pai;

In order to get the results of the sugestoes table that are not in the vertices table. When I use the = sign, it does the correct listing, showing only the same results (author 2 and author 4 of the example), but when I switch to the <> sign, the result I have is all names of authors of the table.

I do not know if the fault is in my logic or the way I'm working on the query.

    
asked by anonymous 02.06.2018 / 10:07

1 answer

1

If your database is MySQL, try the following: 

SELECT autor
FROM sugestoes

MINUS

SELECT s.autor
FROM sugestoes AS s
INNER JOIN vertices AS v ON v.id_node_pai = s.id
    
02.06.2018 / 13:56
Autoload of models in Mongoose AUTO_INCREMENT custom