Search code examples
mariadb

Match multiple combinations of two columns

I have this sample dataset of product reviews. I'm trying to get users who rated certain products positively. Ratings are numbers 1-5, 5 being the best and 1 being the worst.

dataset

I tried the following queries:

SELECT user, product, rating FROM reviews WHERE user != 1 (product = 173 AND rating > 3) AND (product = 50 AND rating > 3);
SELECT user FROM reviews WHERE user != 1 GROUP BY user HAVING (product = 173 AND rating > 3) AND (product = 50 AND rating > 3);
SELECT user, product, rating FROM reviews WHERE user != 1 AND (product, rating) IN ((173, 4), (50, 4))

None of them work. I'm looking for users who rated product 173, 4 or 5 AND rated product 50, 4 or 5. Its easy to get results where one of the combinations match but I need to match all conditions of product, rating combinations.

I prepared a fiddle: db-fiddle.com

Solution

  • Given that you are using MySQL, we could try the following aggregation approach with tuple syntax:

    SELECT user
FROM reviews
WHERE (product, rating) IN ((173, 4), (173, 5), (50, 4), (50, 5)) AND user <> 1
GROUP BY user
HAVING COUNT(DISTINCT product) = 2;

    The WHERE clause of this query restricts to only records rating products 173 and 50 with ratings of 4 or 5. It then aggregates by user and asserts in the HAVING clause that both products were rated as 4 or 5.