SQL: obtenha produtos de uma categoria, mas também deve estar em outro conjunto de categorias
Atualmente estou preso em uma situação. O cenário é esse. Tenho produtos que podem estar associados a várias categorias. A estrutura de dados é mostrada abaixo:
Products Table:
product_id name
1 Lemon
2 Kiwis
3 Cheese
Produto à tabela de categorias
product_id category_id
1 1
1 2
1 3
2 1
2 3
3 2
3 4
Tabela de categorias (não é necessário na consulta, mas é adicionada aqui para ajudar a visualizar o que está acontecendo)
category_id name
1 Fruit
2 Yellow
3 Round
4 Dairy
O que eu estou enfrentando aqui é que originalmente eu quero pegar todos os produtos que estão na categoria de frutas (id de categoria 1), mas também quero verificar se uma fruta é amarela. Tenha em mente que o amarelo não será o único filtro, às vezes eu quero devolver a fruta amarela e laranja, no entanto, como o queijo é amarelo, não posso devolvê-lo, pois não é uma fruta. No entanto, para tornar as coisas um pouco mais fáceis, eu sempre sei que vou olhar na categoria frutas como base.
A estrutura do banco de dados não pode mudar como uma estrutura de banco de dados aberta.
Aqui estão minhas tentativas:
SELECT GROUP_CONCAT(DISTINCT p2c2.category_id SEPARATOR ',') as categories
FROM oc_product_to_category p2c
LEFT JOIN oc_product p ON (p.product_id = p2c.product_id)
LEFT JOIN oc_product_to_category p2c2 ON (p.product_id = p2c2.product_id)
WHERE p2c.category_id IN ('1','2')
Esse tipo de trabalho, exceto pelo fato de que ele retornará Cheese.
Notas: Eu uso Group Concat, porque no final de tudo isso meu objetivo é retornar não tanto os produtos que correspondem a essas categorias, mas com base nos filtros que eu quero retornar outra lista de categorias dos produtos que correspondem a esse critério. Assim:
Cenário:
Obter produtos que correspondem aos critérios da categoria. Retornar categorias desses produtos.
Qualquer assistência será muito apreciada.