Обсуждение: 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
thanone 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
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti E-Mail: Herbert.Liechti@thinx.ch
ThinX networked business services Stahlrain 10, CH-5200 Brugg
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 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
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're
afterwith 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
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're
afterwith 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 :-(
Thanks anyway
Herbie
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti E-Mail: Herbert.Liechti@thinx.ch
ThinX networked business services Stahlrain 10, CH-5200 Brugg
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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)
Ed Loehr wrote:
>
> >
> > 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)
It is working ;-) Thanks
Herbie
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti E-Mail: Herbert.Liechti@thinx.ch
ThinX networked business services Stahlrain 10, CH-5200 Brugg
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~