pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ed Loehr <ELOEHR@austin.rr.com>
To: pgsql-general@postgresql.org
Subject: Re: sql question
Date: Thu, 18 Nov 1999 10:37:26 -0600
Message-ID: <38342B46.C65ADF61@austin.rr.com> (raw)
References: <199911180404.XAA46562@hub.org>

> Date: Wed, 17 Nov 1999 20:53:33 +0100
> 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);

Hope that helps.

Ed




view thread (71+ messages)  latest in thread

Message-ID: <38342B46.C65ADF61@austin.rr.com>
Permalink:  ../38342B46.C65ADF61@austin.rr.com/
Also on:    postgresql.org/message-id/38342B46.C65ADF61@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
  Subject: Re: sql question
  In-Reply-To: <38342B46.C65ADF61@austin.rr.com>
  References: <199911180404.XAA46562@hub.org> <38342B46.C65ADF61@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