duanliaogui4133
2014-03-02 10:32
浏览 356
已采纳

使用in()的mysql查询不起作用

I am trying to find all products that are connected with category list with this query:

SELECT p.id, product_name, added_date 
FROM products p, products_to_categories ptc 
WHERE ptc.category_id in ("89,88,83,87,84,85,86,82") 
AND ptc.product_id=p.id and p.active="1" 
group by p.id   

if I cut the condition AND ptc.product_id=p.id returns rows - wrong rows for active condition.. what is the correct way to get correct information with 1 query? - is it possible at all? thanks

图片转代码服务由CSDN问答提供 功能建议

我正在尝试使用此查询查找与类别列表相关的所有产品: \ n

  SELECT p.id,product_name,added_date 
FROM products p,products_to_categories ptc 
WHERE ptc.category_id in(“89,88,83,87,84,85,86,82”)
AND ptc  .product_id = p.id和p.active =“1”
group by p.id 
   
 
 

如果我剪切条件 AND ptc.product_id = p.id 返回行 - 活动条件的错误行.. 用1个查询获取正确信息的正确方法是什么? - 它有可能吗? thanks

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • dongpu3792 2014-03-02 10:35
    已采纳

    Don't quote the list of category IDs:

    SELECT p.id, product_name, added_date 
    FROM products p
    JOIN products_to_categories ptc ON ptc.product_id=p.id
    WHERE ptc.category_id IN (89, 88, 83, 87, 84, 85, 86, 82) 
    AND p.active="1" 
    GROUP BY p.id
    
    打赏 评论
  • donglingsai2880 2014-03-02 10:35

    Try removing quoting in IN clause and in p.active if active is int/bool

    $q='SELECT p.id, product_name, added_date 
    FROM products p, products_to_categories ptc 
    WHERE ptc.category_id in (89,88,83,87,84,85,86,82) 
    AND ptc.product_id=p.id and p.active=1
    group by p.id';
    
    打赏 评论
  • douguan3470 2014-03-02 10:37

    Dont encapsulate all categories with brackets. MySQL interprets this as a single category. Encapsulate each category, e.g. "89","90","91". You can also just skip the brackets altogether as it is integers you query on.

    You would encapsulate the ids if they were strings, e.g. "one","two","three".

    打赏 评论

相关推荐 更多相似问题