pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcount() and multiple tables
6+ messages / 3 participants
[nested] [flat]
* count() and multiple tables
@ 2001-03-19 23:37 Joseph Shraibman <jks@selectacast.net>
2001-03-19 23:52 ` Re: count() and multiple tables Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-19 23:52 ` Re: count() and multiple tables Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 6+ messages in thread
From: Joseph Shraibman @ 2001-03-19 23:37 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: count() and multiple tables
2001-03-19 23:37 count() and multiple tables Joseph Shraibman <jks@selectacast.net>
@ 2001-03-19 23:52 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-20 00:02 ` Re: count() and multiple tables Joseph Shraibman <jks@selectacast.net>
1 sibling, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2001-03-19 23:52 UTC (permalink / raw)
To: Joseph Shraibman <jks@selectacast.net>; +Cc: pgsql-sql
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
);
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: count() and multiple tables
2001-03-19 23:37 count() and multiple tables Joseph Shraibman <jks@selectacast.net>
2001-03-19 23:52 ` Re: count() and multiple tables Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-03-20 00:02 ` Joseph Shraibman <jks@selectacast.net>
2001-03-20 01:23 ` Re: count() and multiple tables Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 6+ messages in thread
From: Joseph Shraibman @ 2001-03-20 00:02 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: count() and multiple tables
2001-03-19 23:37 count() and multiple tables Joseph Shraibman <jks@selectacast.net>
2001-03-19 23:52 ` Re: count() and multiple tables Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-20 00:02 ` Re: count() and multiple tables Joseph Shraibman <jks@selectacast.net>
@ 2001-03-20 01:23 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-20 04:46 ` Re: count() and multiple tables Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2001-03-20 01:23 UTC (permalink / raw)
To: Joseph Shraibman <jks@selectacast.net>; +Cc: pgsql-sql
> > 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]
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: count() and multiple tables
2001-03-19 23:37 count() and multiple tables Joseph Shraibman <jks@selectacast.net>
2001-03-19 23:52 ` Re: count() and multiple tables Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-20 00:02 ` Re: count() and multiple tables Joseph Shraibman <jks@selectacast.net>
2001-03-20 01:23 ` Re: count() and multiple tables Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-03-20 04:46 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2001-03-20 04:46 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; Joseph Shraibman <jks@selectacast.net>; +Cc: pgsql-sql
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 information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: count() and multiple tables
2001-03-19 23:37 count() and multiple tables Joseph Shraibman <jks@selectacast.net>
@ 2001-03-19 23:52 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2001-03-19 23:52 UTC (permalink / raw)
To: Joseph Shraibman <jks@selectacast.net>; +Cc: pgsql-sql
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 information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2001-03-20 04:46 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-19 23:37 count() and multiple tables Joseph Shraibman <jks@selectacast.net>
2001-03-19 23:52 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-20 00:02 ` Joseph Shraibman <jks@selectacast.net>
2001-03-20 01:23 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-20 04:46 ` Josh Berkus <josh@agliodbs.com>
2001-03-19 23:52 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox