Re: [sqlite] count() problem.

[Duquette, William H (318K)]

On 6/14/12 1:00 PM, "Igor Tandetnik"  wrote:

>On 6/14/2012 2:00 PM, Duquette, William H (318K) wrote:
>> 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.
>
>Why don't you just say that?
>
>select a_id from a
>where b_id not in (select b_id from c where flag = 1);

I tend not to think in terms of nested selects.  That looks very nice!

>
>-- 
>Igor Tandetnik
>
>___
>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

Re: [sqlite] count() problem.

[Igor Tandetnik]

On 6/14/2012 2:00 PM, Duquette, William H (318K) wrote:

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.


Why don't you just say that?

select a_id from a
where b_id not in (select b_id from c where flag = 1);

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] count() problem.

[Duquette, William H (318K)]

On 6/14/12 11:06 AM, "Pavel Ivanov"  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)
> 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

Re: [sqlite] count() problem.

[Pavel Ivanov]

> 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)
 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] count() problem.

[Duquette, William H (318K)]

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