pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ed Loehr <ELOEHR@austin.rr.com>
To: Herbert Liechti <Herbert.Liechti@thinx.ch>
Cc: postgres <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Re: sql question
Date: Thu, 18 Nov 1999 18:17:54 -0600
Message-ID: <38349732.5F1E85BD@austin.rr.com> (raw)
References: <199911180404.XAA46562@hub.org>
	<38342B46.C65ADF61@austin.rr.com>
	<38346B06.61F74C93@thinx.ch>

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 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)





view thread (71+ messages)  latest in thread

Message-ID: <38349732.5F1E85BD@austin.rr.com>
Permalink:  ../38349732.5F1E85BD@austin.rr.com/
Also on:    postgresql.org/message-id/38349732.5F1E85BD@austin.rr.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: ELOEHR@austin.rr.com, Herbert.Liechti@thinx.ch
  Subject: Re: [GENERAL] Re: sql question
  In-Reply-To: <38349732.5F1E85BD@austin.rr.com>
  References: <199911180404.XAA46562@hub.org> <38342B46.C65ADF61@austin.rr.com> <38346B06.61F74C93@thinx.ch> <38349732.5F1E85BD@austin.rr.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox