I have 2 tables with the following schemas:
t1(id, type) where id and type are the primary key
t2(type, rate) where type is the primary key
I want to find all id's which appear with all types from t2.
For example given the tables:
id | type --------- 1 | a 1 | b 1 | c 2 | a 2 | b
type | rate ----------- a | 100 b | 100 c | 200
We can see that there're 3 unique types in t2 and only id 2 from t1 appears with all of them.
I figured out a way to get the id's by counting the number of unique types and then grouping t1 by id's and selecting only those whose count equals of the total number of types from t2.
But I was wondering if there're other logical constructs like
exists or Cartesian product which can also be used here with more elegance?