Received: from msgin1.anpe.fr (nsmail@smtp1.anpe.fr [195.101.137.20]) by hub.org (8.9.3/8.9.3) with ESMTP id DAA61208 for ; Tue, 8 Feb 2000 03:43:21 -0500 (EST) (envelope-from patrick.jacquot@anpe.fr) Received: from antivirus.anpe.fr ([10.0.4.95]) by msgin1.anpe.fr (Netscape Messaging Server 3.6) with SMTP id AAA4943 for ; Tue, 8 Feb 2000 09:41:26 +0100 Sender: pjacquot Message-ID: <389FDF9A.8596E7C1@anpe.fr> Date: Tue, 08 Feb 2000 10:19:23 +0100 From: Patrick JACQUOT Organization: DSI Noisiel X-Mailer: Mozilla 4.6 [en] (X11; I; Linux 2.2.9-19mdk i586) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgreSQL.org Subject: Re: [SQL] SubSelect as a column References: <27709.949980829@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Tom Lane wrote: > Lars writes: > > I am trying to use a subselect as a column name, but it appears as if this > > is not supported in Postgresql. Here is the query: > > > SELECT u.idnum, u.username, > > (SELECT COUNT(t.techid) > > FROM ticket t > > WHERE t.techid = u.idnum) > > FROM users u; > > Nope, doesn't work in 6.5.*. It is there in current sources for the > upcoming 7.0 release. Can't think of any good workaround in 6.5... > > regards, tom lane > > ************ i would do an UNION of the tuples coming from the joint with the tuples from "users" who don't have a counterpart in "ticket", using "not exists" and adding to them a zero count. Hoping it will help regards P.Jacquot