How to implement MySQL 'NOT EXISTS' query for Contact having 1 Category but not another-CodePudding

I have front-end search form that allows users to search for Contacts given a number of search fields (name, address, email, categories, etc). I now have the requirement to implement 'negative' category searches. In other words, they can search for a Contact belonging to one category, but not another. An example would be to return all Contacts assigned to "category_1" and not assigned to "category_2". They do this by entering in a front-end html form field like so: "category_1, -category_2" (notice the minus sign).

I am attempting to use the NOT EXIST function in MySQL because I need the "negative filter" in the WHERE clause (for several reasons). Below is my attempt to implement. Any idea why this is producing zero results?

For clarity, my table arrangement is as follows (many-to-many): Contact -> ContactCategories <- Category

SELECT DISTINCT
    contact.*
FROM
    Contact contact
    INNER JOIN ContactCategory categories ON contact.ContactId = categories.ContactId
    INNER JOIN Category category ON categories.CategoryId = category.CategoryId
WHERE
    LOWER(category.Name) = 'category_1'
    AND NOT EXISTS ( 
        SELECT DISTINCT
            contact_temp.*
        FROM
            Contact contact_temp
            INNER JOIN ContactCategory categories_temp ON contact_temp.ContactId = categories_temp.ContactId
            INNER JOIN Category category_temp ON categories_temp.CategoryId = category_temp.CategoryId
        WHERE
            LOWER(category_temp.Name) = 'category_2'
    )

CodePudding user response：

The majority of your code is repeated. For that reason I wouldn't use NOT EXISTS()

Instead you can just use a single (non-correlated) sub-query to check both conditions, and join the results on to the the contact table.

Note: I've also removed the DISTINCT keyword. It's often used to hide either faulty queries or faulty data; fix the fault, don't fudge the query.

SELECT
  contact.*
FROM
  Contact contact
INNER JOIN
(
  SELECT
    categories.ContactId
  FROM
    ContactCategory categories
  INNER JOIN
    Category        category
      ON categories.CategoryId = category.CategoryId
  WHERE
    LOWER(category.Name) IN ('category_1', 'category_2')
  GROUP BY
    categories.ContactId
  HAVING
        MAX(CASE WHEN LOWER(category.Name) = 'category_1' THEN 1 END) = 1
    AND MAX(CASE WHEN LOWER(category.Name) = 'category_2' THEN 1 END) = 0
)
  category_search
    ON contact.ContactId = category_search.ContactId

Also, be aware that using LOWER(category.Name) inhibits the use of indexes on that table. You're better off having lower_name as an indexed column and searching that directly, without embedding the LOWER() in your query.

CodePudding user response：

You need to correlate the subquery with the main query.

There's no need to select from Contact in the subquery, just select from ContactCategory and correlate that with categories_temp.ContactId = contact.ContactId.

SELECT DISTINCT
    contact.*
FROM
    Contact contact
    INNER JOIN ContactCategory categories ON contact.ContactId = categories.ContactId
    INNER JOIN Category category ON categories.CategoryId = category.CategoryId
WHERE
    LOWER(category.Name) = 'category_1'
    AND NOT EXISTS ( 
        SELECT *
        FROM
            Contact contact_temp
            INNER JOIN ContactCategory categories_temp ON contact_temp.ContactId = categories_temp.ContactId
            INNER JOIN Category category_temp ON categories_temp.CategoryId = category_temp.CategoryId
        WHERE
            contact_temp.ContactId = contact.ContactId
            AND LOWER(category_temp.Name) = 'category_2'
    )