Thread: Problem with HAVING clause

I have created the following two tables:

CREATE TABLE tab1 (Id INTEGER, Name TEXT);
CREATE TABLE tab2 (Id INTEGER, Attribute INTEGER);

populated as:

INSERT INTO tab1 VALUES (0, 'name 1');
INSERT INTO tab1 VALUES (1, 'name 2');
INSERT INTO tab1 VALUES (2, 'name 3');

INSERT INTO tab2 VALUES (0, 0);
INSERT INTO tab2 VALUES (0, 1);
INSERT INTO tab2 VALUES (0, 4);
INSERT INTO tab2 VALUES (0, 5);
INSERT INTO tab2 VALUES (1, 0);
INSERT INTO tab2 VALUES (2, 0);
INSERT INTO tab2 VALUES (2, 1);

test=> SELECT * FROM tab1;
id|name
--+------
 0|name 1
 1|name 2
 2|name 3
(3 rows)

test=> SELECT * FROM tab2;
id|attribute
--+---------
 0|        0
 0|        1
 0|        4
 0|        5
 1|        0
 2|        0
 2|        1
(7 rows)

and I would like to list the 'names' which are associated to more than 'N'
attributes. For 'N=2' I can get the 'id's by

test=> SELECT Id FROM tab2 GROUP BY Id HAVING COUNT(*) > 2;
id
--
 0
(1 row)

so that I expected to be able to use this information in a subquery like:

test=> SELECT tab1.Name FROM tab1 WHERE tab1.Id IN (SELECT tab2.Id FROM tab2
GROUP BY tab2.Id HAVING COUNT(*) > 2);
ERROR:  rewrite: aggregate column of view must be at rigth side in qual

which gives me nothing but the strange error above. Although I do not completely
understand this message I have rewritten the query so that the aggregate does
appear in the right side of the HAVING clause as:

test=> SELECT tab1.Name FROM tab1 WHERE tab1.Id IN (SELECT tab2.Id FROM tab2
GROUP BY tab2.Id HAVING 2 < COUNT(*));
name
------
name 1
name 2
name 3
(3 rows)

but now what I have is something I really do not want: all names! Notice that the
"inverted" subquery still gives the correct result:

test=> SELECT tab2.Id FROM tab2 GROUP BY tab2.Id HAVING 2 < COUNT(*);
id
--
 0
(1 row)

Is this a bug or did I miss some point? Does anybody have some ideas?
By now I do the job with the join:

test=> SELECT tab1.Name FROM tab1, tab2 WHERE tab1.Id=tab2.Id GROUP BY tab1.Name
HAVING COUNT(*) > 2;
name
------
name 1
(1 row)

I believe this is less efficient than the above since I have to 'join' two tables
before applying the HAVING clause. Any comments?

TIA


P.S.: I am using PostgreSQL v. 6.4.2

--
Mauricio C. de Oliveira
mailto:carvalho@dt.fee.unicamp.br
http://www.dt.fee.unicamp.br/~carvalho

  I'm novice in postgresSQL.
  How do you have a relations with two tables?

  Table 1 ->       cod               int 4             index
                   descrition        varchar(30)
                   price             init 4

  Table 2 ->       order             varchar(8)
                   cod               int 4
                   quant             ini 4


  I'm a expected one select with result:


              order  |  descrition | quant | price | total(price * quant)