How can I write a query that excludes certain results, but includes results that lack the association used for exclusion?

Question

I have a search form used to query service provisions, which I will call 'provisions'. Provisions may have certain eligibility requirements in a has and belongs to many relationship, but they may also have no requirements at all. My issue is that when I exclude certain provisions on the basis of a particular requirement, those provisions that have no requirements at all are also excluded.

Table structure:

provisions
* id
* title

requirements
* id
* title

provisions_requirements
* provision_id
* requirement_id

Suppose a requirement is like:

Canadian Citizenship (id 4)

This would be presented on the form like: "Exclude services that have the requirement of: Canadian Citizenship".

The exclusionary portion of the query would thus be:

requirements.id NOT IN (4)

However, I then only get provisions that have at least one requirement, because those provisions that have no requirements are not included in the join.

The actual query is like:

SELECT DISTINCT `provisions`.id 
FROM `provisions` 
LEFT OUTER JOIN `provisions_requirements` 
  ON `provisions_requirements`.provision_id = `provisions`.id 
LEFT OUTER JOIN `requirements` 
  ON `requirements`.id = `provisions_requirements`.requirement_id 
WHERE (requirements.id NOT IN ('1')) 
LIMIT 0, 10

Answer 1

try this:

SELECT DISTINCT `provisions`.id 
FROM `provisions` 
LEFT OUTER JOIN `provisions_requirements` ON `provisions_requirements`.provision_id = `provisions`.id 
LEFT OUTER JOIN `requirements` ON `requirements`.id = `provisions_requirements`.requirement_id and (requirements.id NOT IN ('1'))
LIMIT 0, 10

Answer 2

So you want all services except those that have the requirement of Canadian Citizenship (req_id 4)?

Try this: it tries to match against the requirement_id of 4, but the condition in the WHERE clause makes it so only provisions that found no such match satisfy the condition.

SELECT p.id
FROM provisions p LEFT OUTER JOIN provisions_requirements r
 ON (p.id = r.provision_id AND r.requirement_id IN (4))
WHERE r.requirement_id IS NULL
LIMIT 0, 10;

No DISTINCT is necessary. No join to the requirements table is necessary.

Answer 3

SELECT
     P.id
FROM
     Provisions P
WHERE
     NOT EXISTS
     (
          SELECT
               *
          FROM
               Provision_Requirements PR
          WHERE
               PR.provision_id = P.id AND
               PR.requirement_id = 4
     )

Answer 4

your query in 99% there, just also include a test for NULL

SELECT DISTINCT `provisions`.id 
FROM `provisions` 
LEFT OUTER JOIN `provisions_requirements` 
  ON `provisions_requirements`.provision_id = `provisions`.id 
LEFT OUTER JOIN `requirements` 
  ON `requirements`.id = `provisions_requirements`.requirement_id 
WHERE (requirements.id NOT IN ('1')) 
or requirements.id is NULL -- test for null
LIMIT 0, 10

this would include those rows with no entry in the provisions_requirements table