Received: from mx99.openu.ac.il (arbel.openu.ac.il [147.233.32.4]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA94403 for ; Wed, 18 Aug 1999 10:20:44 -0400 (EDT) (envelope-from herouth@oumail.openu.ac.il) Received: from talmon.openu.ac.il (talmon.openu.ac.il [147.233.32.11]) by mx99.openu.ac.il (Postfix) with ESMTP for id 4E3594B63D; Wed, 18 Aug 1999 17:20:15 +0300 (IDT) Received: from [147.233.159.109] (mac-herouth.pc.openu.ac.il) by talmon.openu.ac.il (Netscape Messaging Server 3.54) with ESMTP id AAA69C1; Wed, 18 Aug 1999 17:23:54 +0300 X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: In-Reply-To: <24554.934984289@sss.pgh.pa.us> References: Your message of Tue, 17 Aug 1999 22:38:13 -0400 (EDT) <199908180238.WAA12634@candle.pha.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Wed, 18 Aug 1999 17:21:39 +0300 To: Hamid Khoshnevis , pgsql-sql@postgreSQL.org From: Herouth Maoz Subject: Re: [SQL] Using intersect in subquery At 16:51 +0300 on 18/08/1999, Tom Lane wrote: > Bruce Momjian writes: > > Try 6.5. We fixed some Intersect stuff. > >> > >> select * from Table1 > >> where KeyField1 in > >> (select KeyField2 from Table2 where somecondition > >> intersect > >> select KeyField3 from Table3 where somecondition); > >> > >> (I believe) I am running PG 6.4. The INTERSECT by itself works, but >not in > >> a subquery. > >> > > No, it still won't work --- the current grammar specifies SubSelect not > select_clause as the kind of select you can put inside an expression. > Not sure what it would take to fix this; I have a feeling that just > changing the grammar wouldn't be good enough :-(. Better add it to the > TODO list: > * Support UNION/INTERSECT/EXCEPT in sub-selects In the meantime I suppose changing the query might work. Intersect should return only the rows which are returned from both tables, but since we are talking about only one field here, it may be convenient to just do an internal join? SELECT * FROM Table1 WHERE KeyField in ( SELECT Keyfield2 FROM Table2, Table3 WHERE Keyfield2 = Keyfield3 ); This is not equivalent in the case of a NULL, but in all other cases, I believe it is. Or maybe the following is more efficient (and clear?): SELECT * FROM Table1 WHERE EXISTS ( SELECT * FROM Table2 WHERE KeyField2 = KeyField ) AND EXISTS ( SELECT * FROM Table3 WHERE KeyField3 = KeyField ); Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma