Finding orders where products of both types are present

Question

Consider below table tbl:

ordernr productId productType
1       12        A
2       15        B
2       13        C
2       12        A
3       15        B
3       12        A
3       11        D

How can I get only rows where products of both productType's B and C are present in the order? The desired output should be below because products of both type B and C are present in the order:

2       15        B
2       13        C
2       12        A

Answer 1

It might be more efficient to use use exists twice:

select t.*
from mytable t
where 
    exists (select 1 from mytable t1 where t1.ordernr = t.ordernr and t1.productid = 'B')
    and exists (select 1 from mytable t1 where t1.ordernr = t.ordernr and t1.productid = 'C')

This query would take advantage of an index on (ordernr, productid).