CODEWITHSUNDEEP
mysql
Cowwando
Cowwando

Reputation: 460

MYSQL Query search in relationship

For the sake of clarity and this question i will rename the tables so it is a bit clearer for everybody and explain what i want to achieve:

There is an input form with options that return categories ID's. If a 'Product' has 'Category', i want to return/find the 'Product' which lets say has multiple categories(or just 1) and all of its categories are inside the array that is passed from the form.

Products table

ID     Title
1      Pizza
2      Ice Cream

Categories table

ID     Title
1      Baked food
2      Hot food

ProductsCategories table

ID     ProductId     CategoryId
1      1             1  
2      1             2

So if i pass [1,2] the query should return Product with id 1 since all ProductsCategories are inside the requested array, but if i pass only 1 or 2, the query should return no results.

Currently i have the following query which works, but for some reason if i create a second Product and create a ProductCategory that has a CategoryId same as the first product, the query returns nulll...

 SELECT products.*
 FROM products
 JOIN products_categories
 ON products_categories.product_id= products.id
 WHERE products_categories.category_id IN (1, 2)
 HAVING COUNT(*) = (select count(*) from products_categories pc 
     WHERE pc  .product_id = products.id)

All help is deeply appretiated! Cheers!

Upvotes: 2

Views: 230

Answers (2)

Cowwando
Cowwando

Reputation: 460

@mitkosoft, thanks for your answer, but sadly the query is not producing the needed results. If the product's categories are partially in the passed categories the product is still returned. Additionally i might not know how many parameters are sent by the form.

Luckily I managed to create the query that does the trick and works perfectly fine (at least so far)

SELECT products.*,    
COUNT(*) as resultsCount,
(SELECT COUNT(*) FROM products_categories pc WHERE pc.product_id = products.id) as categoriesCount

FROM products
JOIN products_categories AS productsCategories
ON productsCategories.product_id= products.id
WHERE productsCategories.category_id IN (7, 15, 8, 1, 50)

GROUP BY products.id
HAVING resultsCount = categoriesCount
ORDER BY amount DESC #optional

That way the query is flexible and gives me exactly what I needed! - Only those products that have all their categories inside the search parameters(not partially).

Cheers! :)

Upvotes: 0

mitkosoft
mitkosoft

Reputation: 5316

In order to match all values in IN clause, you just need to know in addition the number of passed categories which you must use it in HAVING clause:

SELECT
    p.*, 
    GROUP_CONCAT(c.title) AS categories
FROM
    Products p
    INNER JOIN ProductsCategories pc ON pc.productId = p.ID
    INNER JOIN Categories c ON c.ID = pc.categoryId
WHERE
    pc.categoryId IN (1,2)
GROUP BY
    p.id
HAVING
    COUNT(DISTINCT pc.categoryId) = 2 -- this is # of unique categories in IN clause

So in case IN (1,2) result is:

+----+-------+---------------------+
| id | title | categories          |
+----+-------+---------------------+
|  1 | Pizza | Baked Food,Hot Food |
+----+-------+---------------------+
1 row in set

In case IN (1,3) result is Empty set (no results).

Upvotes: 1

Related Questions