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.

Answer 2

Your explanation is not very clear. I think you want to get those type_uid's from table A where for all records in table B there is a matching A.Allowed_type_uid.

SELECT T2.type_uid
FROM (SELECT COUNT(*) as AllAllowedTypes FROM #B) as T1,
    (SELECT #A.type_uid, COUNT(*) as AllowedTypes
    FROM #A
        INNER JOIN #B ON
            #A.allowed_type_uid = #B.type_uid
    GROUP BY #A.type_uid
    ) as T2
WHERE T1.AllAllowedTypes = T2.AllowedTypes

(Dems, you were faster than me :) )

Answer 3

Or a slightly cheaper version (resource wise):

SELECT 
    Data.type_uid
FROM 
    A AS Data
    CROSS JOIN
    B
    LEFT JOIN
    A
    ON Data.type_uid = A.type_uid AND B.type_uid = A.allowed_type_uid
GROUP BY 
    Data.type_uid
HAVING 
    MIN(ISNULL(A.allowed_type_uid,-999)) != -999