MySQL data must exist in two columns to be returned

Question

I'm stuck on the best way to achieve this MySQL query.

I have three tables:

products - this is a list of products

id | product_name

cats - this is a list of categories

id | category_name

cats_selected - multiple categories can be assigned to a product, this table lists them

cat_id | product_id

In a search a user can select multiple categories to search on at once. I want to check that a product exists in each category, so it needs to match cat_id and product_id. The cat id's are posted as an array.

What is the best way to query this data?

For example, the following doesn't work for obvious reasons:

SELECT *
FROM products
JOIN cats_selected
ON cats_selected.product_id = products.id
AND cats_selected.cat_id = '16372' 
AND cats_selected.cat_id = '9'

Answer 1

SELECT p.id
FROM products p
JOIN cats_selected s ON s.product_id = p.id
where s.cat_id in (16372, 9)
group by p.id
having count(distinct s.cat_id) = 2

Answer 2

Does this work? Your query syntax is not right.

SELECT *
FROM products
JOIN cats_selected
ON cats_selected.product_id = products.id
WHERE cats_selected.cat_id IN ('16372', '9')

Answer 3

Assuming your cat_id's array looks something like [1,2,4,3,52] you can use the IN function, and you need to have cats_selected.cat_id check in the WHERE part

$cat_ids = [1,2,4,3,52];
$catImplode = implode(",",$cat_ids);

$query = <<<END
   SELECT products.*, GROUP_CONCAT(cats_selected.cat_id) as cats
   FROM products
   JOIN cats_selected
   ON cats_selected.product_id = products.id
   WHERE cats_selected.cat_id IN ($catImplode)
   GROUP BY products.id
END;

This would end up with $query being:

SELECT products.*, GROUP_CONCAT(cats_selected.cat_id) as cats
FROM products
JOIN cats_selected
ON cats_selected.product_id = products.id
WHERE cats_selected.cat_id IN (1,2,4,3,52)
GROUP BY products.id

Added a GROUP_CONCAT column aliased to cats in case you need to know what categories where matched for the product