Received: from server4.mpcbbs.com.br (server4.mpc.com.br [200.246.0.252]) by hub.org (8.9.2/8.9.1) with ESMTP id OAA83265 for ; Mon, 22 Feb 1999 14:52:21 -0500 (EST) (envelope-from carvalho@dt.fee.unicamp.br) Received: from dt.fee.unicamp.br (mau@d2p53.mpcnet.com.br [200.246.29.181]) by server4.mpcbbs.com.br (8.8.8/8.8.6) with ESMTP id QAA21744 for ; Mon, 22 Feb 1999 16:52:16 -0300 (EST) Sender: mau@mpc.com.br Message-ID: <36D1B5B9.A3C8ACD9@dt.fee.unicamp.br> Date: Mon, 22 Feb 1999 16:53:29 -0300 From: Mauricio Carvalho de Oliveira X-Mailer: Mozilla 4.5 [en] (X11; I; Linux 2.0.29 i586) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@hub.org Subject: Problem with HAVING clause References: <199902220400.XAA82447@hub.org> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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