On 1/12/2011 1:59 PM, Ed Nolan wrote: > ------- this fails (no result) > ---------------------------------------------------- > > CREATE TABLE f_main (id INTEGER PRIMARY KEY,pathid INTEGER,name TEXT); > INSERT INTO f_main VALUES(4,5,"my_filename"); > > CREATE TABLE f_path (pathid INTEGER PRIMARY KEY,name TEXT); > INSERT INTO f_path VALUES(5,"md5"); > > CREATE TABLE f_stats (id INTEGER PRIMARY KEY,size INTEGER,md5 TEXT); > INSERT INTO f_stats VALUES(4,973,"16f5a3c8edc1668d7318f6113b810009"); > > SELECT s.id,s.size,s.md5 > FROM f_main m,f_stats s,f_path p > WHERE m.name="my_filename" AND p.name="md5" AND m.pathid=p.pathid AND > m.id=s.id;
In SQL, single quotes 'like this' are used to enclose string literals, while double quotes "like this" are used to enclose identifiers (the latter is to allow identifiers that include spaces or other special characters, or are the same as SQL keywords). As an extension, SQLite accepts string literals enclosed in double quotes. It first checks whether the string matches any identifier present in the query, and if not, assumes it to be a string literal. Thus, in your SELECT statement "md5" is not a string literal, but a reference to f_stats.md5 column. It so happens that the condition p.name=s.md5 doesn't hold for any rows, hence you get no results. I suggest you get into the habit of consistently using single quotes around string literals in SQL. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
