A new filter operator "has all of"

If Table A is linked to Table B in a 1 to n cardinality, it would be nice to have a filter that can search for records in A that has multiple matching records in B.

Say for example there are 2 tables "Products" and "Product Categories". One product can belong to multiple categories. In the report builder user should be able to filter for Products that belong to "Category A" and "Category B" without using 2 filters (one for Category A and Category B).

If there is a filter operator similar to "In list" (instead of "ANY", it will be "ALL") that can search for all Products that belong to all categories specified in the list, it will solve this problem.

Did some research online to figure out a way you design the query for this and found the below result. First solution is most commonly used, but 2nd one looks nice. It would be nice to have this type of filter in our product

This type of problem is called relational division.

There are two common solutions:

• First solution strings together the matching categories and compares to a fixed string:

SELECT p2c.product_id

FROM oc_product_to_category p2c

GROUP BY p2c.product_id

HAVING GROUP_CONCAT(p2c.category_id SEPARATOR ',' ORDER BY p2c.category_id) = '1,2'

• Second solution does a JOIN for each required value:

SELECT p.product_id

FROM oc_product p

INNER JOIN oc_product_to_category p2c1

ON (p.product_id = p2c1.product_id AND p2c1.category_id = 1)

INNER JOIN oc_product_to_category p2c2

ON (p.product_id = p2c2.product_id AND p2c2.category_id = 2)|

URL: http://stackoverflow.com/questions/17662603/sql-get-products-from-a-category-but-also-must-be-in-another-set-of-categories