MySQL: Can I use INNER JOIN to find Items of a table belonging to a category defined in a second table?

Question

I have two tables imc_shop and imc_shop_category. The imc_shop_category reflects the categories a certain item of the shop belongs to.

In my example the shop table has two items with id 10 and 11. In the category table item 10 points to category 3, 11, 16, 19 and 31 where item 11 points to 31, 3, 12, 16 and 19.

If I search for items, which belong to category 3 and 12 only item 11 should be returned.

I tried the following query, which did not work:

SELECT s.* FROM imc_shop AS s
 INNER JOIN imc_shop_category AS c ON s.itemId = c.itemId
 WHERE c.categoryId = 3 AND c.categoryId = 12

Any idea how to get the correct result?

Answer 1

SELECT s.* FROM imc_shop AS s
 INNER JOIN imc_shop_category AS c ON s.itemId = c.itemId
 AND (c.categoryId = 3 OR c.categoryId = 12)
GROUP BY s.itemID
HAVING COUNT(DISTINCT c.categoryId) = 2