You have given me plenty of food for thought. Thank you for taking the time.
Currently, the tables have such few records (350, 900, 1000) that performance does not come into it, particularly seeing as this was only needed for a one-shot report.
However, I have stached your examples away for future reference.
I was feeling a bit guilty about posting such a trivial question. I can cobble together some straightforward SQL but I could really do with a source of more complex SQL examples.
If you know of any links - that would great and save the list from more such questions ;-)
I am correcting a couple of typos below in case someone tries these examples out.
Greg Stark wrote:
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
... having sum(case when type_name = 'xyz' ...
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)
) as somealias on (model_fk = model_pk)
(subquery in FROM must have an alias)
--
Regards/Gruß,
Tarlika
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
