在这个查询MYSQL中失败了什么

时间：2022-09-06 00:06:45

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

以下查询仅在我在表wm_purchased_products.purchased_article_id中有引用时才有效,但是当它为空时,mysql_num_rows返回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

如何在表中没有记录时构建此查询wm_purchased_products.purchased_article_id

1 个解决方案

#1

2  

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.)

您的代码FROM wm_products_wall,wm_products_quantities,wm_purchased_products意味着所有三个表都是INNER JOIN。(也就是说,第一个表中的每一行都连接到第二个表中的每一行,依此类推。)

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

你可以让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

#1

2  

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.)

您的代码FROM wm_products_wall,wm_products_quantities,wm_purchased_products意味着所有三个表都是INNER JOIN。(也就是说,第一个表中的每一行都连接到第二个表中的每一行,依此类推。)

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

你可以让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
标签：

相关文章