Re: [GENERAL] Re: sql question - Mailing list pgsql-general
From | Herbert Liechti |
---|---|
Subject | Re: [GENERAL] Re: sql question |
Date | |
Msg-id | 3834F70F.FB590B95@thinx.ch Whole thread Raw |
In response to | Re: sql question (Ed Loehr <ELOEHR@austin.rr.com>) |
List | pgsql-general |
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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
pgsql-general by date: