Re: [GENERAL] Re: sql question - Mailing list pgsql-general

Herbert Liechti wrote:

> Ed Loehr wrote:
>
> > > From: Herbert Liechti <Herbert.Liechti@thinx.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
morethan 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'reafter 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)