Re: COUNT(*) to find records which have a certain number of dependencies ?

T E Schmitz <mailreg(at)numerixtechnology(dot)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