From: | Ed Loehr <ELOEHR(at)austin(dot)rr(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: sql question |
Date: | 1999-11-18 16:37:26 |
Message-ID: | 38342B46.C65ADF61@austin.rr.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> Date: Wed, 17 Nov 1999 20:53:33 +0100
> 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);
Hope that helps.
Ed
From | Date | Subject | |
---|---|---|---|
Next Message | Miguel Montes | 1999-11-18 17:30:15 | Problems with datetime |
Previous Message | Bruce Momjian | 1999-11-18 14:42:24 | Publisher looking for Reviewers |