I have a database with two tables, one of which depends on the other:
CREATE TABLE a(
a_id INTEGER PRIMARY KEY
b_id INTEGER);
CREATE TABLE c(
b_id INTEGER,
num INTEGER,
flag INTEGER,
PRIMARY KEY (b_id,num));
In words, each "a" is associated with a "b", and the "b" has any number of
"c"'s associated it with. (There's also a "b" table, which I've omitted.)
Table c might not have any rows for a specific b_id. In the rows it has, the
flag column might be 0 or 1.
What I want to do is find a_id's for which c contains no rows with the matching
b_id in which the flag column is 1. I'm using a query like this:
SELECT a_id, count(*)
FROM a LEFT OUTER JOIN c USING (b_id)
WHERE c.flag = 1
GROUP BY a_id
I get a positive count if there are there are matching rows with flag = 1; but
if an a_id has no matching rows in c with flag=1, I get nothing at all. What I
want is a row with the a_id and a count of 0.
What am I doing wrong? Is there an easy way to do this?
Thanks!
--
Will Duquette -- [email protected]
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
