From: Herbert Liechti <Herbert.Liechti@thinx.ch>
To: postgres <pgsql-general@postgreSQL.org>
Cc: Ed Loehr <ELOEHR@austin.rr.com>
Subject: Re: [GENERAL] Re: sql question
Date: Thu, 18 Nov 1999 22:09:27 +0100
Message-ID: <38346B06.61F74C93@thinx.ch> (raw)
References: <199911180404.XAA46562@hub.org>
<38342B46.C65ADF61@austin.rr.com>
Ed Loehr wrote:
> > From: Herbert Liechti <Herbert.Liechti@thinx.ch>
> > Subject: sql question
> >
> > I have a sql problem which I can't solve. The following table is defined
> >
> > create table AdressGroup
> > (
> > AdrGroup_Id INTEGER NOT NULL
> > DEFAULT NEXTVAL('adrverw_id_seq'),
> > Zeit DATETIME NOT NULL,
> > Group_Id INTEGER NOT NULL,
> > Adress_Id INTEGER NOT NULL
> > );
> >
> > The table assigns adresses to adressgroups.
> >
> > I'd like to select the adresses which belongs to one specific adressGroup and to no other group. If an adress has more than one entry in the AdressGroup
> > table it should not be in the projection.
> >
> > I tried the following:
> >
> > SELECT * FROM adressGroup
> > WHERE Group_Id = 6
> > AND EXISTS( SELECT AdrGroup_Id FROM adressGroup ag_alias
> > WHERE adressGroup.AdrGroup_Id = ag_alias.AdrGroup_Id
> > GROUP BY AdrGroup_Id HAVING COUNT(*) = 1 );
> >
> > When I do this I get the following error:
> > ERROR: rewrite: aggregate column of view must be at rigth side in qual
> > ERROR: rewrite: aggregate column of view must be at rigth side in qual
> >
> > Does anybody knows the solution? Thanks
> >
>
> Not sure about the error above, other than the apparent typos with "adressGroup", but I think you can get what you're after with this:
>
> SELECT *
> FROM AdressGroup ag
> WHERE ag.Group_Id = 6 AND NOT EXISTS (
> SELECT AdrGroup_Id
> FROM AddressGroup ag2
> WHERE ag2.AdrGroup_Id = ag.AdrGroup_Id AND ag2.Group_Id <> 6);
The query does not produce the estimated result :-/( It returns all records from
adressgroup 6. It seems that the problem can not be solved in a single sql-statement.
I assume that the above statement joins the table with the alias table with the
subset of adress group 6.
Probably I do it with a temporary table:
Create temp table tempGroup(adrGroup_id integer, groupcounter integer);
INSERT INTO tempGroup
SELECT adrGroup_Id, count(*) FROM adrGroup GROUP BY 1 HAVING COUNT(*) = 1);
SELECT * from adrGroup, tempGroup
WHERE adrGroup.adrGroup_id = tempGroup.adrGroup_id
AND adrGroup.group_id = 7;
This is working but without the desired performance :-(
Thanks anyway
Herbie
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti E-Mail: Herbert.Liechti@thinx.ch
ThinX networked business services Stahlrain 10, CH-5200 Brugg
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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-general@postgresql.org
Cc: Herbert.Liechti@thinx.ch, ELOEHR@austin.rr.com
Subject: Re: [GENERAL] Re: sql question
In-Reply-To: <38346B06.61F74C93@thinx.ch>
References: <199911180404.XAA46562@hub.org> <38342B46.C65ADF61@austin.rr.com> <38346B06.61F74C93@thinx.ch>
* 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