此查询 MYSQL 失败的原因是什么

发布于 2024-11-08 17:15:45 字数 962 浏览 1 评论 0原文

以下查询仅当我在表 wm_purchased_products.purchased_article_id 中有引用时才有效，但当该表为空时 mysql_num_rows 返回 0

查询是：

  SELECT (SUM(wm_products_quantities.new_quantity) - SUM(wm_purchased_products.purchased_article_total) ) AS stock_restante, 
         wm_products_wall.nombre, 
         wm_products_wall.detalles, 
         wm_products_wall.price, 
         wm_products_wall.image_full, 
         wm_products_wall.fecha, 
         wm_products_wall.article_hashid
    FROM wm_products_wall,
         wm_products_quantities, 
         wm_purchased_products 
   WHERE wm_products_wall.categoria = '$new_rquery_xp' 
     AND wm_products_wall.article_hashid = wm_products_quantities.hashid_ref 
     AND wm_products_wall.article_hashid = wm_purchased_products.purchased_article_id 
GROUP BY wm_products_wall.article_hashid 
ORDER BY stock_restante ASC

当表中没有记录时如何构建此查询以使其工作wm_purchased_products.purchased_article_id

原文

The following query only work when I have a reference in the table wm_purchased_products.purchased_article_id but when this is empty mysql_num_rows return 0

The query is:

  SELECT (SUM(wm_products_quantities.new_quantity) - SUM(wm_purchased_products.purchased_article_total) ) AS stock_restante, 
         wm_products_wall.nombre, 
         wm_products_wall.detalles, 
         wm_products_wall.price, 
         wm_products_wall.image_full, 
         wm_products_wall.fecha, 
         wm_products_wall.article_hashid
    FROM wm_products_wall,
         wm_products_quantities, 
         wm_purchased_products 
   WHERE wm_products_wall.categoria = '$new_rquery_xp' 
     AND wm_products_wall.article_hashid = wm_products_quantities.hashid_ref 
     AND wm_products_wall.article_hashid = wm_purchased_products.purchased_article_id 
GROUP BY wm_products_wall.article_hashid 
ORDER BY stock_restante ASC

How to build this query to work when I have no record in the table wm_purchased_products.purchased_article_id

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

被你宠の有点坏 2024-11-15 17:15:45

您的代码 FROM wm_products_wall, wm_products_quantities, wm_purchased_products 表示所有三个表都是 INNER JOIN 相互连接。（也就是说，第一个表中的每一行都连接到第二个表中的每一行，依此类推。）

您可以让 wm_products_wall LEFT JOIN 与 wm_purchased_products，wm_purchased_products 也是如此。

SELECT 
(SUM(wm_products_quantities.new_quantity) - SUM(wm_purchased_products.purchased_article_total) ) AS stock_restante, 
wm_products_wall.nombre, wm_products_wall.detalles, wm_products_wall.price, 
wm_products_wall.image_full, wm_products_wall.fecha, 
wm_products_wall.article_hashid
FROM wm_products_wall
LEFT OUTER JOIN wm_products_quantities
  ON wm_products_wall.article_hashid = wm_products_quantities.hashid_ref 
LEFT OUTER JOIN wm_purchased_products
  ON wm_products_wall.article_hashid = wm_purchased_products.purchased_article_id
WHERE wm_products_wall.categoria = '$new_rquery_xp' 
GROUP BY wm_products_wall.article_hashid 
ORDER BY stock_restante ASC

Your code FROM wm_products_wall, wm_products_quantities, wm_purchased_products means all three table are INNER JOIN with each other.(that is, each and every row in the first table is joined to each and every row in the second table and so on.)

You can let wm_products_wall LEFT JOIN with wm_purchased_products, so do wm_purchased_products.

SELECT 
(SUM(wm_products_quantities.new_quantity) - SUM(wm_purchased_products.purchased_article_total) ) AS stock_restante, 
wm_products_wall.nombre, wm_products_wall.detalles, wm_products_wall.price, 
wm_products_wall.image_full, wm_products_wall.fecha, 
wm_products_wall.article_hashid
FROM wm_products_wall
LEFT OUTER JOIN wm_products_quantities
  ON wm_products_wall.article_hashid = wm_products_quantities.hashid_ref 
LEFT OUTER JOIN wm_purchased_products
  ON wm_products_wall.article_hashid = wm_purchased_products.purchased_article_id
WHERE wm_products_wall.categoria = '$new_rquery_xp' 
GROUP BY wm_products_wall.article_hashid 
ORDER BY stock_restante ASC

回复收藏 0 原文

~没有更多了~