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 TAA86863 for ; Thu, 18 Nov 1999 19:15:48 -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 18:08:13 -0600 Sender: ed Message-ID: <38349732.5F1E85BD@austin.rr.com> Date: Thu, 18 Nov 1999 18:17:54 -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: Herbert Liechti CC: postgres Subject: Re: [GENERAL] Re: sql question References: <199911180404.XAA46562@hub.org> <38342B46.C65ADF61@austin.rr.com> <38346B06.61F74C93@thinx.ch> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Herbert Liechti wrote: > 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 :-( > I meant to use Address_Id where I used adrGroup_id. Sorry 'bout that. Try this, it seems to work: SELECT * FROM AddressGroup ag1 WHERE NOT EXISTS ( SELECT * FROM AddressGroup ag2 WHERE ag1.Address_Id = ag2.Address_Id AND ag1.Group_Id <> ag2.Group_Id); A working demonstration is below. Cheers, Ed mydb=> create sequence adrverw_id_seq; CREATE mydb=> create table AddressGroup mydb-> ( mydb-> AdrGroup_Id INTEGER NOT NULL DEFAULT NEXTVAL('adrverw_id_seq'), mydb-> Zeit DATETIME NOT NULL, mydb-> Group_Id INTEGER NOT NULL, mydb-> Address_Id INTEGER NOT NULL mydb-> ); CREATE mydb=> insert into AddressGroup(Zeit,Group_Id,Address_Id) values ('now',5,1); NOTICE: adrverw_id_seq.nextval: sequence was re-created NOTICE: adrverw_id_seq.nextval: sequence was re-created INSERT 3669904 1 mydb=> insert into AddressGroup(Zeit,Group_Id,Address_Id) values ('now',6,1); INSERT 3669905 1 mydb=> insert into AddressGroup(Zeit,Group_Id,Address_Id) values ('now',6,2); INSERT 3669906 1 mydb=> insert into AddressGroup(Zeit,Group_Id,Address_Id) values ('now',6,3); INSERT 3669907 1 mydb=> insert into AddressGroup(Zeit,Group_Id,Address_Id) values ('now',7,3); INSERT 3669908 1 mydb=> mydb=> SELECT * FROM AddressGroup; adrgroup_id|zeit |group_id|address_id -----------+----------------------------+--------+---------- 1|Thu Nov 18 18:10:50 1999 CST| 5| 1 2|Thu Nov 18 18:10:50 1999 CST| 6| 1 3|Thu Nov 18 18:10:50 1999 CST| 6| 2 4|Thu Nov 18 18:10:50 1999 CST| 6| 3 5|Thu Nov 18 18:10:50 1999 CST| 7| 3 (5 rows) mydb=> mydb=> SELECT * mydb-> FROM AddressGroup ag1 mydb-> WHERE NOT EXISTS ( mydb-> SELECT * mydb-> FROM AddressGroup ag2 mydb-> WHERE ag1.Address_Id = ag2.Address_Id AND mydb-> ag1.Group_Id <> ag2.Group_Id); adrgroup_id|zeit |group_id|address_id -----------+----------------------------+--------+---------- 3|Thu Nov 18 18:10:50 1999 CST| 6| 2 (1 row)