On 6/14/12 11:06 AM, "Pavel Ivanov" <paiva...@gmail.com> wrote:
>> 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 I had to do this: SELECT a_id, count(num) FROM a LEFT OUTER JOIN c ON a.b_id = c.b_id AND c.flag = 1 GROUP BY a_id With "count(*)" it's counting the rows with a non-null a_id. With "count(num)", it's counting the rows with a non-null "num", which is what I want. Thanks, this was extremely helpful! Will > > >On Thu, Jun 14, 2012 at 2:00 PM, Duquette, William H (318K) ><william.h.duque...@jpl.nasa.gov> 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 -- william.h.duque...@jpl.nasa.gov >> Athena Development Lead -- Jet Propulsion Laboratory >> "It's amazing what you can do with the right tools." >> >> _______________________________________________ >> 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
