SQL query to check a table against a "rules table" I have a table PIT that looks like this:

PIT_TYPE    PIT_STYPE   PIT_APID
1           1           1313
1           7           1313
3           25          1316
247         1           1318
247         2           1318
247         4           1318
247         5           1318
5           2           1319
5           3           1319


I have a rules table that defines the rules for PIT. The rules table looks like this:

Type    SType   Category
1               1
3         26    8
3         25    5
3         25    4
5               7
6               9
9               0
13             10
17         2    7
21             12
22             11


PIT_APID mod 16 = Category E.g. 1313 mod 16 = 1

The rules specify following:


If PIT_TYPE / PIT_SType match no rule in the rules table --> No error
If PIT_TYPE / PIT_SType match a rule in the rules table and the category matches --> No error
If PIT_TYPE / PIT_SType match a rule in the rules table but the category is different --> Error


(If SType is empty in the rules table we don't care about the PIT_STYPE in PIT. Type is never empty)

How can I write a SQL query that returns all the rows in PIT that have an error according to the defined rules?

What I have tried is:

SELECT PIT.PIT_TYPE, tblCategoryRules.Service, PIT.PIT_STYPE, tblCategoryRules.Sub_Service, PIT.PIT_APIT , PIT.PIT_APIT MOD(16) as PCAT, tblCategoryRules.Category
FROM PIT
INNER JOIN tblCategoryRules
ON PIT.PIT_TYPE =  tblCategoryRules.Service
WHERE  (tblCategoryRules.Sub_Service IS NULL or  tblCategoryRules.Sub_Service = PIT.PIT_STYPE) AND tblCategoryRules.Category &lt;&gt; PIT.PIT_APIT MOD(16)


This works for everything except the rule that

Type    SType   Category
3         25    5
3         25    4


The rule that it can either be 5 or 4 is what causes the problem
I'm not real familiar with Access syntax, but I think the general idea you're after would be something like this... Instead of filtering for only records that don't meet your requirements, add a calculation to your select list that indicates if the record/ruleset combination resulted in a pass or fail.  Then group by your other selected columns, and and look for groups that have no passing records and at least one failing record.