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
