Have Geek Brain

How can I write a query that excludes certain results, but includes results that do not have the association used to exclude?

I have a search form used to request terms of service, which I will refer to as "terms". Provisions may have specific eligibility requirements for a and belong to many relationships, but they may also have no requirements at all. My problem is that when I exclude certain clauses based on a specific requirement, those clauses that have no requirement are also excluded.

Table structure:

provisions
* id
* title

requirements
* id
* title

provisions_requirements
* provision_id
* requirement_id

Suppose the requirement is:

Canadian Citizenship (id 4)

This will be presented in the form, for example: "Exclude services that require: Canadian citizenship."

The exclusive part of the request would be like this:

requirements.id NOT IN (4)

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

The actual request looks like 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 
WHERE (requirements.id NOT IN ('1')) 
LIMIT 0, 10
+1


a source to share


4 answers


So, do you need all services except those with a Canadian citizenship requirement (req_id 4)?

Try this: it tries to match with requirement_id

out of 4, but the condition in the sentence WHERE

makes it so that only the conditions that do not match 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

. No table join required requirements

.

+3


a source 


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
     )


+1


a source


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
-1


a source


your query is 99%, just include 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 will include those rows without an entry in the rules_requirements table

-1


a source






More articles:


All Articles