The whitelist_id will be NULL if the JOIN doesn't match thus inverting the match. I want rows that don't match on the JOIN.
In the first example only one row is returned. In the second example all rows are returned. The example with one row is the one that is correct. Row 6 is the only row that should be returned because there is no filter for either of its values. insert into programs(program_id,host_id,name,publisher) VALUES (6,1,"pqr", "stu"); In order for that row to be filtered we would have to have one of the following entries in the whitelist: insert into whitelist(whitelist_id,value1,value2) VALUES (1,"pqr","stu"); insert into whitelist(whitelist_id,value1,value2) VALUES (1,"","stu"); insert into whitelist(whitelist_id,value1,value2) VALUES (1,"pqr",""); Here's the two examples again without the scans and the hosts tables. http://sqlfiddle.com/#!7/4d0d9/2 WebSQL(SQLite) (This one works) http://sqlfiddle.com/#!5/4d0d9/1 SQLite(SQL.js) (Click "Cancel" if prompted.) (This one does not work) On Thu, Jul 11, 2013 at 8:23 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 10 Jul 2013, at 6:30pm, compscilaw . <bluebaroncan...@gmail.com> wrote: > > > I'm getting unexpected and differing results between WebSQL(SQLite) and > > SQLite. The query produces the correct results in WebSQL. > > I cannot answer your question but I have some of my own. > > First, can you remove the tables scans and hosts from your sample code ? > They don't seem to play any part in your SELECT. > > Second, I notice that you have defined > > > CREATE TABLE whitelist( > > whitelist_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, > > > and that your entries in whitelist do not have NULL values for > whitelist_id. Yet your SELECT specifies > > > WHERE w.whitelist_id IS NULL > > What do you expect to happen here ? That column can't be null. > > Third, can you identify one of the rows in programs you didn't expect to > see in the result of the SELECT, so we know what we're investigating ? > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
