Geek asks and answers

SQL query to search for products matching a set of categories

I have 3 tables: products, categories and pro_cat_link. A product can be associated with one or more categories through the pro_cat_link table.

My query should answer the following problem: find all products matching a set of categories. Example: find all products that are "yellow and fruity and sweet."

When investigating this problem in SO, I could only find the solution that I am currently using: Complex SQL query - searching for elements matching several different foreign keys

In my case, my query looks like this:

SELECT products.id, COUNT(DISTINCT categories.id) as countCat
FROM products
INNER JOIN pro_cat_link ON (pro_cat_link.product_id = products.id)
WHERE pro_cat_link.category_id IN (3,6,8,10)
GROUP BY product.id
ORDER BY product.date DESC
HAVING countCat = 4

In other words, select all products that match one of the category identifiers (3,6,8,10), and save only those that have exactly 4 categories.

, , COUNT(), GROUP BY, ORDER BY . - ?

+5
2

, - . total_categories, , . where total_categories = 4. , , - , ...

, , .

+2

, , , (.. 1 , i, 0 ). AND . , , .

, , . 1 1, 3, 5, 6, 8, 10, 1010110101. Item2 1, 2, 4, 6, 8, 10, 1010101011. 3, 6, 8 10 s = 1010100100. Item1 & s = 1010100100 = s. Item2 & s = 1010100000 <> s.

, , 10 . , Item1, Item2 s 693, 683 676. 693 & 676 = 676, 683 & 676 = 672. , i, 2 ^ (i-1), i, 2 ^ (i-1).

, , MySQL int, . , FrustratedWithFormsDes , pro_cat_link, (, , pro_cat_link, ). , , .

+1

More articles:


All Articles