Received: from outthere.thinx.ch (outthere.thinx.ch [194.158.233.34]) by hub.org (8.9.3/8.9.3) with ESMTP id CAA47074 for ; Fri, 19 Nov 1999 02:41:13 -0500 (EST) (envelope-from Herbert.Liechti@thinx.ch) Received: from venus.net.thinx.ch (root@dyna-ol-15.dial.eunet.ch [193.72.192.238]) by outthere.thinx.ch (8.9.2/8.9.2) with ESMTP id IAA19997; Fri, 19 Nov 1999 08:40:54 +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 IAA00408; Fri, 19 Nov 1999 08:40:47 +0100 Sender: herbie@eunet.ch Message-ID: <3834F70F.FB590B95@thinx.ch> Date: Fri, 19 Nov 1999 08:06:56 +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: Ed Loehr , postgres Subject: Re: [GENERAL] Re: sql question References: <199911180404.XAA46562@hub.org> <38342B46.C65ADF61@austin.rr.com> <38346B06.61F74C93@thinx.ch> <38349732.5F1E85BD@austin.rr.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Ed Loehr wrote: > > > > > 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) It is working ;-) Thanks Herbie -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Herbert Liechti E-Mail: Herbert.Liechti@thinx.ch ThinX networked business services Stahlrain 10, CH-5200 Brugg ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~