Thread: count() and multiple tables

count() and multiple tables

From
Joseph Shraibman
Date:
I want to select all the entries from d that have at least one
corresponding entry in u that meets my conditions.  The problem is that
count(*) is returning the number of corresponding entries in u, and I
want only the number of entries in d.  How do I do this?


create table d(              id int  primary key,                 status int default 1
);

create table a(              key int primary key,                  status int default 1
);

create table u(       dkey int not null,akey int not null,b bool DEFAULT false,       status int default 1,primary key
(dkey,akey) 
 
);

insert into d values (1, 2);

insert into a values (1, 3);
insert into a values (2, 3);
insert into a values (3, 3);

insert into u values(1,1,false,2);
insert into u values(1,2,false,1);
insert into u values(1,3,false,2);

select count(*) from d where status = 2 and d.id = u.dkey and u.status =
2 and not u.b and u.akey = a.key and a.status = 3;

/* that returns 2 when I want it to return 1 */

drop table d;
drop table a;
drop table u;


-- 
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com


Re: count() and multiple tables

From
"Josh Berkus"
Date:
Joseph,
SImple as pie (e.g., easy on your 100th one):

> select count(*) from d where status = 2 and d.id = u.dkey and
> u.status =
> 2 and not u.b and u.akey = a.key and a.status = 3;

Count(*) will always count the number of records in the largest table in
your join.  If you want the number of records in a specific table, then
you have to make sure that that table is the only on in your FROM
clause, and reference all other tables in WHERE:

select count(*) from d
where id IN (select u.dkey from u, a where u.akey = a.key    AND u.status = 2 and not u.b    and a.status = 3);

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 


Re: count() and multiple tables

From
Joseph Shraibman
Date:
Stephan Szabo wrote:
> 
> On Mon, 19 Mar 2001, Joseph Shraibman wrote:
> 
> > I want to select all the entries from d that have at least one
> > corresponding entry in u that meets my conditions.  The problem is that
> > count(*) is returning the number of corresponding entries in u, and I
> > want only the number of entries in d.  How do I do this?
> >
> >
> > create table d(
> >                id int  primary key,
> >                   status int default 1
> > );
> >
> > create table a(
> >                key int primary key,
> >                    status int default 1
> > );
> >
> > create table u(
> >         dkey int not null,
> >       akey int not null,
> >       b bool DEFAULT false,
> >         status int default 1,
> >       primary key (dkey, akey)
> > );
> >
> > insert into d values (1, 2);
> >
> > insert into a values (1, 3);
> > insert into a values (2, 3);
> > insert into a values (3, 3);
> >
> > insert into u values(1,1,false,2);
> > insert into u values(1,2,false,1);
> > insert into u values(1,3,false,2);
> >
> > select count(*) from d where status = 2 and d.id = u.dkey and u.status =
> > 2 and not u.b and u.akey = a.key and a.status = 3;
> 
> And postgres tries to be helpful again... :(  [I *really* dislike this
> adding to from list thing]  Technically the above should be illegal
> because no from list contains u or a.  Postgres is adding them to the
> from list for you.
> 
I get the same result if I do:
select count(d.id) from d where status = 2 and d.id = u.dkey and
u.status = 2 and not u.b and u.akey = a.key and a.status = 3;

So in standard SQL all the tables you join accross are required to be in
the FROM?

> I think you want something like (untested):
> select count(*) from d where status=2 and
> exists (
>  select * from u, a where u.dkey=d.id and u.status=2 and
>   no u.b and u.akey=a.key and a.status=3
> );

That works, but I thought there might be a better way because it looks
like that will get all the data out of the table and throw it away right
after.

-- 
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com


Re: count() and multiple tables

From
Stephan Szabo
Date:

On Mon, 19 Mar 2001, Joseph Shraibman wrote:

> I want to select all the entries from d that have at least one
> corresponding entry in u that meets my conditions.  The problem is that
> count(*) is returning the number of corresponding entries in u, and I
> want only the number of entries in d.  How do I do this?
> 
> 
> create table d(
>                id int  primary key,
>                   status int default 1
> );
> 
> create table a(
>                key int primary key,
>                    status int default 1
> );
> 
> create table u(
>         dkey int not null,
>     akey int not null,
>     b bool DEFAULT false,
>         status int default 1,
>     primary key (dkey, akey) 
> );
> 
> insert into d values (1, 2);
> 
> insert into a values (1, 3);
> insert into a values (2, 3);
> insert into a values (3, 3);
> 
> insert into u values(1,1,false,2);
> insert into u values(1,2,false,1);
> insert into u values(1,3,false,2);
> 
> select count(*) from d where status = 2 and d.id = u.dkey and u.status =
> 2 and not u.b and u.akey = a.key and a.status = 3;

And postgres tries to be helpful again... :(  [I *really* dislike this
adding to from list thing]  Technically the above should be illegal
because no from list contains u or a.  Postgres is adding them to the
from list for you.

I think you want something like (untested):
select count(*) from d where status=2 and
exists (select * from u, a where u.dkey=d.id and u.status=2 and no u.b and u.akey=a.key and a.status=3
);



Re: count() and multiple tables

From
Stephan Szabo
Date:
> > And postgres tries to be helpful again... :(  [I *really* dislike this
> > adding to from list thing]  Technically the above should be illegal
> > because no from list contains u or a.  Postgres is adding them to the
> > from list for you.
> > 
> I get the same result if I do:
> select count(d.id) from d where status = 2 and d.id = u.dkey and
> u.status = 2 and not u.b and u.akey = a.key and a.status = 3;
> 
> So in standard SQL all the tables you join accross are required to be in
> the FROM?

Basically, yes.  It's more complicated than that probably (what isn't in
SQL), but that's the general idea.

Postgres assumes your query is
select count(*) from d,u,a ...

Because d.id was guaranteed to be unique, you might be able to 
count(distinct d.id) and get the result you want. [I think the 
subquery is a nicer way of representing it]




Re: count() and multiple tables

From
"Josh Berkus"
Date:
Stephan, Joseph,

> Because d.id was guaranteed to be unique, you might be able to 
> count(distinct d.id) and get the result you want. [I think the 
> subquery is a nicer way of representing it]

Plus a several SQL implementations don't implement the DISTINCT until
after the COUNT, reuslting in a still-inflated (and sometimes varying!)
COUNT.  It won't work properly in T-SQL or MS Office SQL, for example.

-Josh Berkus


______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco