To: mailreg@numerixtechnology.de
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: COUNT(*) to find records which have a certain number of
	dependencies ?
References: <414EF3E6.9040202@numerixtechnology.de>
In-Reply-To: <414EF3E6.9040202@numerixtechnology.de>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 20 Sep 2004 14:32:17 -0400
Message-ID: <87llf4hjv2.fsf@stark.xeocode.com>
Lines: 54
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


T E Schmitz <mailreg@numerixtechnology.de> writes:

> 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'.


There are lots of approaches to this with various pros and cons.

The simplest one off the top of my head:

select * 
  from brand 
  join model on (brand_pk = brand_fk)
 where exists (select 1 from type where model_fk = model_pk and type_name = 'xyz')
   and (select count(*) from type where model_fk = model_pk) > 1


You could try to be clever about avoiding the redundant access to the type table:

select * 
  from brand 
  join model on (brand_pk = brand_fk)
 where (select count(*)
          from type 
         where model_fk = model_pk 
        having sum(case when type = 'xyz' then 1 else 0 end) >= 1
       ) > 1

I'm haven't tested that, it might need some tweaking. In any case I don't
think it's worth the added complexity, assuming you have indexes on type. I'm
not even sure it would run faster.

You could try to be really clever about it by turning the whole thing into a
join:

select * 
  from brand 
  join model on (brand_pk = brand_fk)
  join (select model_fk
          from type 
         group by model_fk
        having sum(case when type = 'xyz' then 1 else 0 end) >= 1
           and count(*) > 1
       ) on (model_fk = model_pk)

This would let the planner have a more plans to choose from and might be a big
win if there are lots of brands and models but few that satisfy the criteria
you're looking for.

-- 
greg