Received: from Mail.austin.rr.com (sm2.texas.rr.com [24.93.35.55]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA95091 for ; Thu, 18 Nov 1999 11:35:21 -0500 (EST) (envelope-from ELOEHR@austin.rr.com) Received: from austin.rr.com ([24.93.40.9]) by Mail.austin.rr.com with Microsoft SMTPSVC(5.5.1877.197.19); Thu, 18 Nov 1999 10:27:45 -0600 Sender: ed Message-ID: <38342B46.C65ADF61@austin.rr.com> Date: Thu, 18 Nov 1999 10:37:26 -0600 From: Ed Loehr X-Mailer: Mozilla 4.51 [en] (X11; I; Linux 2.2.5-15smp i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-general@postgresql.org Subject: Re: sql question References: <199911180404.XAA46562@hub.org> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit > Date: Wed, 17 Nov 1999 20:53:33 +0100 > From: Herbert Liechti > 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); Hope that helps. Ed