> 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.
To achieve that you should do the following: SELECT a_id, count(*) FROM a LEFT OUTER JOIN c ON a.b_id = c.b_id AND c.flag = 1 GROUP BY a_id Pavel On Thu, Jun 14, 2012 at 2:00 PM, Duquette, William H (318K) <[email protected]> wrote: > 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 _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
