X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2B849329E91 for ; Mon, 27 Sep 2004 10:45:22 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 89264-07 for ; Mon, 27 Sep 2004 09:45:15 +0000 (GMT) Received: from msgdirector3.onetel.net.uk (msgdirector3.onetel.net.uk [212.67.96.159]) by svr1.postgresql.org (Postfix) with ESMTP id A72AA329E86 for ; Mon, 27 Sep 2004 10:45:15 +0100 (BST) Received: from bolek.coachhouse (213-78-119-184.friaco.onetel.net.uk [213.78.119.184]) by msgdirector3.onetel.net.uk (MOS 3.4.6-GR) with ESMTP id CTB01139; Mon, 27 Sep 2004 10:45:11 +0100 (BST) Received: (Exim 3.36) #1 (Debian)) protocol: esmtp id 1CBs45-0001fD-00 ; Mon, 27 Sep 2004 10:45:01 +0100 Message-ID: <4157E11D.2030400@numerixtechnology.de> Date: Mon, 27 Sep 2004 10:45:01 +0100 From: T E Schmitz Reply-To: mailreg@numerixtechnology.de User-Agent: Mozilla Thunderbird 0.7 (Windows/20040616) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Martin Knipper , pgsql-sql@postgresql.org Subject: Re: COUNT(*) to find records which have a certain number of References: <414F02FF.8000603@numerixtechnology.de> <414F08FD.3080200@mk-os.de> In-Reply-To: <414F08FD.3080200@mk-os.de> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/236 X-Sequence-Number: 19082 Hallo Martin, Martin Knipper wrote: > Am 20.09.2004 18:19 schrieb T E Schmitz: > >>>I want to select only those BRAND/MODEL combinations, where the MODEL >>>has more than one TYPE, but only where one of those has TYPE_NAME='xyz'. >>>I am not interested in MODELs with multiple TYPEs where none of them are >>>called 'xyz'. >> >> > > Try this: > > select b.brand_name,m.model_name,count(*) from model m inner join > brand b on m.brand_fk=b.brand_pk inner join type t on > m.model_pk=t.model_fk where t.type_name='xyz' group by > b.brand_name,m.model_name having count(m.model_name)>0; > Sorry, no, this doesn't work. It definitely doesn't return the required resultset. It just returns all BRAND_NAME,MODEL_NAME for all Models that have a Type with TYPE_NAME='xyz'. -- Regards/Gruß, Tarlika Elisabeth Schmitz