php - MySQL Left Join with WHERE 和 Greater than

Question

我想做一个 LEFT JOIN，然后按 rank 列对我的显示进行排序，并且仅当 qty 列为大于 0

SELECT 
*
FROM `product`
LEFT JOIN `stock`
ON `product`.`product_id`=`stock`.`pid`
AND `qty` > 1
ORDER BY `product`.`rank` ASC

产品表

product_id
name
rank
price

库存表

pid
price_sale
qty

Product Table

12, Pen, 2, 53.00
13, Pen, 1, 58.00
14, Pen, 3, 25.00

Stock Table

12, 10.00, 5 
13, 18.00, 15

我的结果没有出现。

它应该显示:

12, Pen, 2, 53.00, 12, 10.00, 5
13, Pen, 1, 58.00, 13, 18.00, 15

Answer 1

使用下面的查询

SELECT * FROM `product`
LEFT JOIN `stock`
ON `product`.`product_id`=`stock`.`pid`
WHERE `stock`.`qty` > 0
ORDER BY `product`.`rank` ASC