Thread: sql question

sql question

From
Herbert Liechti
Date:
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~




Re: sql question

From
Ed Loehr
Date:
> 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


Re: [GENERAL] Re: sql question

From
Herbert Liechti
Date:
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~




Re: [GENERAL] Re: sql question

From
Ed Loehr
Date:
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)



Re: [GENERAL] Re: sql question

From
Herbert Liechti
Date:
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~