Hi,
I'm using SQLite 2.8.13 (3.x not possible; not installed at the target host). I am trying a LEFT JOIN query which does not do what I expect.
Take two tables, one with articles, and one with article authors. The first table has an ide per article, and the second table can contain 0 or more per article id, and each of those rows has an author id.
sqlite> SELECT * FROM d_article_author WHERE d_author = 521;
gives me 5 rows:
12088|6|521 12163|2|521 12166|3|521 12595|6|521 12831|3|521
Now doing
sqlite> SELECT a.o_id,b.o_id,b.d_author
FROM d_article a
LEFT JOIN d_article_author b ON (a.o_id = b.o_id)
WHERE b.d_author = 521 LIMIT 0,10;gives me:
11761|| 11762|| 11763|| 11764|| 11765|| 11766|| 11767|| 11768|| 11769|| 11770||
That's not right: those NULLs should not match the condition on d_author?
As a workaround, I can do:
sqlite> SELECT a.o_id,b.o_id,b.d_author
FROM d_article a
LEFT JOIN d_article_author b ON (a.o_id = b.o_id)
WHERE b.d_author = 521 AND b.d_author IS NOT NULL LIMIT 0,10;which returns:
12088|12088|521 12163|12163|521 12166|12166|521 12595|12595|521 12831|12831|521
which is what I expected. Am I missing something? The query without the IS NOT NULL behaves as expected on MySQL.
Regards,
Herman
----------------------------------------------------------------- Herman Kuiper - m: [EMAIL PROTECTED] - w: http://www.frontier.nl Beech Ave 162 - 1119 PS Schiphol-Rijk - t/f: 020-6589034/6142816
