Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by hub.org (8.9.2/8.9.1) with ESMTP id JAA38275; Wed, 24 Mar 1999 09:55:38 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id JAA26237; Wed, 24 Mar 1999 09:54:49 -0500 (EST) To: Matthew cc: "'pgsql-general@hub.org'" , "'pgsql-interfaces@hub.org'" Subject: Re: [INTERFACES] Problem using Having in a sub-query wit the Count function. In-reply-to: Your message of Tue, 23 Mar 1999 12:46:10 -0600 Date: Wed, 24 Mar 1999 09:54:49 -0500 Message-ID: <26235.922287289@sss.pgh.pa.us> From: Tom Lane Matthew writes: >> "Select ordnum from ordinace where dept='Finance' and ordnum in >> (Select ordnum from squareview where square='A') and ordnum in (select >> ordnum from keywordview where keyword='Parade' or keyword='Public >> Gathering' group by ordnum having count(ordnum) =2)" I wonder whether the parser could be getting confused by the multiple distinct uses of the same name "ordnum" in this query? In other words, maybe you'd have better luck if the inner queries read something like select k.ordnum from keywordview k where k.keyword='Parade' or k.keyword='Public Gathering' group by k.ordnum having count(k.ordnum) =2 Without that, it might be thinking that count(ordnum) refers to the ordnum in the outer select. If that is it, it's probably a bug, but I'm not sure what the standard says about how to interpret ambiguous names in this context... regards, tom lane