The Problem;
Let’s describe a MySQL database system which is going to used for searching the product we can make with the materials we have.
Table of Products
+-------+-------------+ | id | product | +-------+-------------+ | 1 | product1 | +-------+-------------+ | 2 | product2 | +-------+-------------+ | 3 | product3 | +-------+-------------+ | 4 | product4 | +-------+-------------+
Table of Materials
+-------+--------------+ | id | material | +-------+--------------+ | 1 | material1 | +-------+--------------+ | 2 | material2 | +-------+--------------+ | 3 | material3 | +-------+--------------+ | 4 | material4 | +-------+--------------+ | 5 | material5 | +-------+--------------+ | 6 | material6 | +-------+--------------+
Table of Material needs for Products (Many To Many)
+-------+-------------+-------------+ | id | productid | materialid | +-------+-------------+-------------+ | 1 | 1 | 1 | +-------+-------------+-------------+ | 2 | 1 | 2 | +-------+-------------+-------------+ | 3 | 1 | 3 | +-------+-------------+-------------+ | 4 | 1 | 4 | +-------+-------------+-------------+ | 5 | 2 | 1 | +-------+-------------+-------------+ | 6 | 2 | 2 | +-------+-------------+-------------+ | 7 | 2 | 3 | +-------+-------------+-------------+ | 8 | 3 | 3 | +-------+-------------+-------------+ | 9 | 3 | 4 | +-------+-------------+-------------+ | 10 | 3 | 5 | +-------+-------------+-------------+ | 11 | 4 | 5 | +-------+-------------+-------------+ | 12 | 4 | 6 | +-------+-------------+-------------+
These are just examples of the database structure. With these kinds of tables and data, the system needs to perform a search. And the real database will be more complex and crowded.
Example Search;
+----------------------------------------------+--------------------------+ | search | result | +----------------------------------------------+--------------------------+ | material1, material2, material3, material4 | product1, product2 | +----------------------------------------------+--------------------------+ | material1, material2, material3 | product2 | +----------------------------------------------+--------------------------+ | material3, material4, material5, material6 | product3, product4 | +----------------------------------------------+--------------------------+ | material5, material6 | product4 | +----------------------------------------------+--------------------------+ | material2, material3, material4, material5 | product3 | +----------------------------------------------+--------------------------+ | material1, material2, material3 | product2 | +----------------------------------------------+--------------------------+ | material4, material5, material6 | product4 | +----------------------------------------------+--------------------------+ | material1, material2 | - | +----------------------------------------------+--------------------------+
In words, the search will give the result with the product names based on the material names we give as input.
The Solution;
This is an example of a “set-within-sets” query. The most flexible way of answering it is to use aggregation with a having clause:
SET @LISTOFMATERIALS = 'material1,material2,...'
The variable @LISTOFMATERIALS has a list of materials to start the search with. It is a comma-separated string of material names such as 'material1,material2,...' with no spaces after the comma.
SELECT p.*
FROM tableofmaterialneeds tomn
JOIN products p
ON tomn.productid = p.productid
JOIN tableofmaterials tom
ON tomn.materialid = tom.materialid
GROUP BY p.productid
HAVING sum(find_in_set(tomn.material, @LISTOFMATERIALS) = 0) = 0; The find_in_set() returns 0 only when a material is not in the set. The sum() counts the number of materials, and the = 0 is saying that there are none.