pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Joseph Shraibman <jks@selectacast.net>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: count() and multiple tables
Date: Mon, 19 Mar 2001 19:02:23 -0500
Message-ID: <3AB69E0F.4A489EBB@selectacast.net> (raw)
References: <Pine.BSF.4.21.0103191549230.26890-100000@megazone23.bigpanda.com>

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



view thread (6+ messages)  latest in thread

Message-ID: <3AB69E0F.4A489EBB@selectacast.net>
Permalink:  ../3AB69E0F.4A489EBB@selectacast.net/
Also on:    postgresql.org/message-id/3AB69E0F.4A489EBB@selectacast.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: jks@selectacast.net, sszabo@megazone23.bigpanda.com
  Subject: Re: count() and multiple tables
  In-Reply-To: <3AB69E0F.4A489EBB@selectacast.net>
  References: <Pine.BSF.4.21.0103191549230.26890-100000@megazone23.bigpanda.com> <3AB69E0F.4A489EBB@selectacast.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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