Received: from orion.SAPserv.Hamburg.dsh.de (Tpolaris2.sapham.debis.de [53.2.131.8]) by hub.org (8.9.2/8.9.1) with SMTP id QAA34247 for ; Thu, 4 Feb 1999 16:45:43 -0500 (EST) (envelope-from wieck@sapserv.debis.de) Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-sql@postgreSQL.org id m108WeP-000EBPC; Thu, 4 Feb 99 22:49 MET Message-Id: From: jwieck@debis.com (Jan Wieck) Subject: Re: [SQL] problem with join & count To: pat@patoche.org Date: Thu, 4 Feb 1999 22:49:28 +0100 (MET) Cc: pgsql-sql@postgreSQL.org Reply-To: jwieck@debis.com (Jan Wieck) In-Reply-To: <199902042039.VAA00553@vagabond.bde.espci.fr> from "pat@patoche.org" at Feb 4, 99 09:39:28 pm X-Mailer: ELM [version 2.4 PL25] Content-Type: text > i need an sql query that will return: > > name | how_often > ------------------- > toto | 2 > titi | 1 > tutu | 2 > > that is, the result table should contain all names present in the first table > and then the number of times the number associated with the name appears in > the second table. Postgres does not (and v6.5 will not) support outer joins or subselects in the targetlist, what's required to do that in a single SQL statement. What you could do is using a SQL function that covers the count() like this: create function num_refs (int4) returns int4 as ' select count(*) from tab2 where id = $1; ' language 'SQL'; select item_name, num_refs(id) as how_often from tab1; I changed 'number' into id and name into item_name because both are reserved words. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #======================================== jwieck@debis.com (Jan Wieck) #