Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA78884 for ; Mon, 10 Apr 2000 15:51:09 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id PAA17331; Mon, 10 Apr 2000 15:50:27 -0400 (EDT) To: "Robert Nosko" cc: pgsql-sql@postgresql.org Subject: Re: How to do this in PostgreSQL? In-reply-to: References: Comments: In-reply-to "Robert Nosko" message dated "Mon, 10 Apr 2000 19:38:19 -0700" Date: Mon, 10 Apr 2000 15:50:27 -0400 Message-ID: <17328.955396227@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200004/95 "Robert Nosko" writes: > In ORACLE I can perform the following query: > SELECT tableA.id, title, qty > FROM tableA, (SELECT id, count(*) qty FROM tableB group by id) tableC > WHERE tableA.id = tableC.id (+) > but in PostgreSQL I get error message. We don't currently have sub-SELECTs in FROM (maybe in 7.1 though). I doubt we ever will support that nonstandard "(+)" syntax (that's Oracle-speak for an outer join, no?). But 7.0 does handle sub-SELECTs in the result list, so you could do SELECT id, title, (SELECT count(*) FROM tableB where tableB.id = tableA.id) AS qty FROM tableA; This will be slower than the other way, probably, but it works and is at least as readable. regards, tom lane