php - mysql 字段组合搜索

Question

表结构

类别表

id      name 
23      Rifle   
24      Bow 
26      Black Powder

服装商表

id    name
1     jignesh
2     Ram
3     Ravi

服装商类别表

id  outfitters_id  categories_id
1    1             23
2    1             24
3    1             26

4    2             23
5    2             26

6    3             26

现在我的问题是何时选择(步枪和黑火药)意味着(23,26)结果应该是

1     jignesh
2     Ram

当前查询

SELECT distinct outfitters.id,
outfitters.name,
FROM outfitters 
LEFT JOIN categories as c1 ON (c1.categories_id='23') 
LEFT JOIN categories as c2 ON (c2.categories_id='26') 
WHERE outfitters.id=c1.outffiters_id 
OR 
outfitters.id=c2.outffiters_id 
AND outfitters.of_active=1

Answer 1

如果您需要必须属于您的 2 个类别的服装商，您可以通过获取必须 > 或 = 2 的每个服装商的不同计数以及您的 where 过滤器来完成此操作，请根据提供的示例数据集查看 fiddle 示例

SELECT 
  o.* 
FROM
  Outfitters o 
  JOIN outfitters_categories oc 
    ON (o.id = oc.outfitters_id) 
WHERE oc.categories_id IN (23, 26) 
GROUP BY o.id 
HAVING COUNT(DISTINCT oc.categories_id) >= 2 

See Demo