[SOLVED] Select distinct from one table only if all rows exist in another tables

Select distinct from one table only if all rows exist in another tables

I have three tables; StoreAttributes and TemplateCondtions are both connected in a 1-to-many relationship with the ConditionAttributes table.

ConditionAttributes

PK CondiitionAttributeId int
Name varchar(100)

StoreAttributes

PK Id uniqueidentifier
StoreNbr string
ConditionAttributeId FK (From Condition Attributes)

TemplateConditons

PK Id uniqueidentifier
TemplateId uniqueIdentifier
ConditionAttribureId FK (From Condition Attributes)

Example data set:

StoreNbr    WalkConditionAttributeId
------------------------------------
5705        1
5705        2
5707        2
5705        3
5706        3

Id       TemplateId WalkConditionAttributeId
--------------------------------------------
105      78         1
109      78         2
500      78         3

I'd like to select only store 5705 because it matches all WalkCondtionsAttributeId for TemplateId = 78.

So far I've tried this:

Select Distinct StoreNbr 
From StoreAttributes st
Where WalkConditionAttributeId in (Select ConditionAttributeId 
                                   From TemplateConditions 
                                   Where TemplateId = 78);

This returns the wrong StoreNbr because they contain at least one row in the TemplateConditions table.

Solution

This is classic Relational Division With Remainder. There are many solutions, here is one:

SELECT
  sa.StoreNbr
FROM StoreAttributes sa
JOIN (
    SELECT tc.*,
      COUNT(*) OVER (PARTITION BY tc.TemplateId) AS count
    FROM TemplateConditons tc
    WHERE tc.TemplateId = 78
) tc ON tc.ConditionAttributeId = sa.ConditionAttributeId
GROUP BY
  sa.StoreNbr
HAVING COUNT(*) = MIN(tc.count);

db<>fiddle

The way I have written it (using a windowed count) makes it easy to remove the TemplateId predicate, and therefore make it a question of RDWR With Multiple Divisors.