CODEWITHSUNDEEP
mysqlsql
Darren Taylor
Darren Taylor

Reputation: 2025

MySQL Where In with Condition

I've been knocking my head against a brick wall with this query for hours and thought I'd chance my luck here.

I'm working on a query which returns Products based on Categories and Category Ratings.

A simplified schema would look like:

products
id
name
price

categories
id
name

products_categories
product_id
category_id
rating ( 0 - 5 )

The query I currently have is as follows:

SELECT 
`products` . *, products_categories.*
FROM
`products`
    JOIN
`products_categories` ON (`products`.`id` = `products_categories`.`product_id`)
WHERE
`products_categories`.`category_id` IN ('5' , '24', '41')
    AND `products_categories`.`rating` > 1
    AND `products`.`enabled` = 1
HAVING count(distinct (products_categories.category_id)) = 3
ORDER BY `amount` ASC
LIMIT 9 OFFSET 0

The first part of the query is to select all Products which belong to those 3 categories which works fine, however, the part I'm struggling with is the rating part.

I need the query to select all of the Products which belong to those 3 categories IF the categories have a rating of 1 or greater. As it stands, the query only operates on the rating of the last category which returns bogus results.

Any input would be greatly appreciated, even if it's just a point in the right direction.

Cheers.

Upvotes: 0

Views: 1985

Answers (1)

Barmar
Barmar

Reputation: 780974

You need a GROUP BY clause so that COUNT() will count by product, rather than counting everything at once.

SELECT 
`products` . *, products_categories.*
FROM
`products`
    JOIN
`products_categories` ON (`products`.`id` = `products_categories`.`product_id`)
WHERE
`products_categories`.`category_id` IN ('5' , '24', '41')
    AND `products_categories`.`rating` > 1
    AND `products`.`enabled` = 1
GROUP BY products.id
HAVING count(distinct (products_categories.category_id)) = 3
ORDER BY `amount` ASC
LIMIT 9 OFFSET 0

Upvotes: 1

Related Questions