Received: from outthere.thinx.ch (outthere.thinx.ch [194.158.233.34]) by hub.org (8.9.3/8.9.3) with ESMTP id QAA39654 for ; Thu, 18 Nov 1999 16:40:16 -0500 (EST) (envelope-from Herbert.Liechti@thinx.ch) Received: from venus.net.thinx.ch (root@dyna-ol-2.dial.eunet.ch [193.72.192.225]) by outthere.thinx.ch (8.9.2/8.9.2) with ESMTP id WAA18740; Thu, 18 Nov 1999 22:39:26 +0100 (CET) Received: from thinx.ch (herbie@tobi.net.thinx.ch [192.168.0.9]) by venus.net.thinx.ch (8.8.8/8.8.8) with ESMTP id WAA04961; Thu, 18 Nov 1999 22:39:20 +0100 Sender: herbie@eunet.ch Message-ID: <38346B06.61F74C93@thinx.ch> Date: Thu, 18 Nov 1999 22:09:27 +0100 From: Herbert Liechti Organization: ThinX networked business services X-Mailer: Mozilla 4.51 [en] (X11; I; Linux 2.2.5 i686) X-Accept-Language: en MIME-Version: 1.0 To: postgres CC: Ed Loehr Subject: Re: [GENERAL] Re: sql question References: <199911180404.XAA46562@hub.org> <38342B46.C65ADF61@austin.rr.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Ed Loehr wrote: > > 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); 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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~