Re: [GENERAL] Re: sql question

Herbert Liechti wrote:

> Ed Loehr wrote:
>
> > > From: Herbert Liechti <Herbert(dot)Liechti(at)thinx(dot)ch>
> > > 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)