Find Customers Who Meet Every Requirement

Finding customers with any required capability is easy: join their capabilities to a requirement list. Finding customers with every required capability is a different question. A join proves that at least one matching row exists; it does not prove completeness. This distinction appears in entitlement checks, certification reports, product bundles, and migration readiness lists.

Translate all into no missing requirement

A useful formulation is: keep a customer when no required item is missing. The outer NOT EXISTS searches for a requirement that fails. The inner NOT EXISTS asks whether that customer lacks the particular item. Although the double negative looks unusual, each level corresponds to a precise business condition.

CREATE TABLE #Customers(CustomerId int PRIMARY KEY);
CREATE TABLE #Required(Capability varchar(10) PRIMARY KEY);
CREATE TABLE #Has(CustomerId int,Capability varchar(10),
 PRIMARY KEY(CustomerId,Capability));
INSERT #Customers VALUES(1),(2),(3);
INSERT #Required VALUES('A'),('B');
INSERT #Has VALUES(1,'A'),(1,'B'),(1,'C'),(2,'A');
SELECT c.CustomerId FROM #Customers AS c
WHERE NOT EXISTS
(SELECT 1 FROM #Required AS r WHERE NOT EXISTS
 (SELECT 1 FROM #Has AS h WHERE h.CustomerId=c.CustomerId
  AND h.Capability=r.Capability))
ORDER BY c.CustomerId;

Customer 1 qualifies because capabilities A and B both exist; the extra C does not disqualify it. Customer 2 lacks B and fails. Customer 3 has no capabilities and fails. The primary keys state that each membership and each required capability appears once, avoiding ambiguity about duplicate rows.

To debug the result, run the missing-item query. It turns a yes/no outcome into an actionable list of gaps. This is often more useful to an operations team than a percentage whose denominator is not visible.

SELECT c.CustomerId,r.Capability AS MissingCapability
FROM #Customers AS c CROSS JOIN #Required AS r
WHERE NOT EXISTS(SELECT 1 FROM #Has AS h
 WHERE h.CustomerId=c.CustomerId AND h.Capability=r.Capability)
ORDER BY c.CustomerId,r.Capability;

Decide what the empty set means

If there are no requirements, the first query returns all customers. There is no missing requirement for any of them. That is the usual logical meaning of all, but it may not match a workflow where an unconfigured requirement set should block approval. If configuration must exist, add a separate EXISTS check for the requirement table rather than changing the logic accidentally.

All required items also differs from exactly the required items. The example accepts customer 1 despite capability C. For exact membership, add another anti-existence test rejecting customer capabilities outside the required set. Define this explicitly before implementing compliance or access decisions; unwanted extras may be either harmless or disqualifying.

Avoid nullable membership identifiers unless their meaning is specified. Equality does not match two NULL values in the same way it matches two ordinary identifiers. A missing or unknown capability code should generally be rejected or reconciled before this query, not silently counted as satisfying an unknown requirement.

Compare alternatives without changing meaning

A GROUP BY with COUNT(DISTINCT Capability) can also solve the problem if it counts only required capabilities and compares against the correct requirement count. Counting all customer capabilities is wrong: two unrelated capabilities do not satisfy two required capabilities. Duplicate membership rows can also inflate a plain COUNT if the schema does not enforce uniqueness.

The customer-capability key supports checking membership for a specific customer and item. For workloads driven primarily by a small capability set, a complementary capability-customer index may help, but measure its read benefit against write and storage costs. Use actual plans on representative customer counts and requirement-set sizes; do not assume nested NOT EXISTS must execute as nested procedural loops.

When requirements or memberships change during evaluation, choose a consistent data boundary for decisions that must be reproducible. Record the requirement version with an approval result if later reviewers need to reconstruct why it passed. A current-state query cannot explain yesterday's decision after the rules change.

Validate no requirements, no memberships, an exact match, a missing item, extras, and duplicate input rejected by constraints. The successful implementation both selects the right customers and explains their missing requirements without conflating any, all, and exactly.

Technical references: Microsoft Learn: EXISTS · Microsoft Learn: COUNT.