Miha Vrhovnik wrote:
using sqlite 3.2.8.
CREATE TABLE movies (id INTEGER PRIMARY KEY, catalogIDNumeric
INTEGER);
INSERT INTO movies (catalogIDNumeric) VALUES(1);
INSERT INTO movies (catalogIDNumeric) VALUES(2);
INSERT INTO movies (catalogIDNumeric) VALUES(3);
INSERT INTO movies (catalogIDNumeric) VALUES(5);
INSERT INTO movies (catalogIDNumeric) VALUES(7);
INSERT INTO movies (catalogIDNumeric) VALUES(8);
CREATE TEMP TABLE tmpCatalogID (catalogIDNumeric INTEGER);
INSERT INTO tmpCatalogID VALUES(1);
INSERT INTO tmpCatalogID VALUES(2);
INSERT INTO tmpCatalogID VALUES(3);
INSERT INTO tmpCatalogID VALUES(4);
INSERT INTO tmpCatalogID VALUES(5);
INSERT INTO tmpCatalogID VALUES(6);
INSERT INTO tmpCatalogID VALUES(7);
INSERT INTO tmpCatalogID VALUES(8);
query returns empty resultset instead of number 4:
SELECT MIN(catalogIDNumeric) FROM tmpCatalogID WHERE NOT EXISTS
(SELECT catalogIDNumeric FROM movies);
Why should it return number 4? The subquery under NOT EXISTS very
obviously returns a non-empty set (movies table is not empty). Perhaps
you meant
SELECT MIN(catalogIDNumeric) FROM tmpCatalogID tmp
WHERE NOT EXISTS (
SELECT * FROM movies m
where m.catalogIDNumeric = tmp.catalogIDNumeric
);
Igor Tandetnik
