SQL, only if matching all foreign key values to return the record?

Question

I have two tables

Table A
type_uid, allowed_type_uid
9,1
9,2
9,4
1,1
1,2
24,1
25,3

Table B
type_uid
1
2

From table A I need to return

9
1

Using a WHERE IN clause I can return

9
1
24

Answer 1

SELECT
  TableA.type_uid
FROM
  TableA
INNER JOIN
  TableB
    ON TableA.allowed_type_uid = TableB.type_uid
GROUP BY
  TableA.type_uid
HAVING
  COUNT(distinct TableB.type_uid) = (SELECT COUNT(distinct type_uid) FROM TableB)

Join the two tables togeter, so that you only have the records matching the types you are interested in.

Group the result set by TableA.type_uid.

Check that each group has the same number of allowed_type_uid values as exist in TableB.type_uid.

distinct is required only if there can be duplicate records in either table. If both tables are know to only have unique values, the distinct can be removed.

It should also be noted that as TableA grows in size, this type of query will quickly degrade in performance. This is because indexes are not actually much help here.

It can still be a useful structure, but not one where I'd recommend running the queries in real-time. Rather use it to create another persisted/cached result set, and use this only to refresh those results as/when needed.