加入SQL查询问题

| 我有以下查询,该查询返回产品和找到的最低售价以及该售价的数量。在我想要获得在product_price表中没有任何价格的产品之前,一切工作正常。我怎样才能让它返回产品价格和NULL的SellPrice和数量?
SELECT p.*, MIN(pp.sellPrice) as sellPrice, pp.quantity FROM `product` as p
LEFT JOIN `product_price_group` as ppg ON ppg.productId = p.`id`
LEFT JOIN `product_price` as pp ON pp.priceGroupId = ppg.`id`
WHERE p.`id` = 1 AND p.`active` = 1
具有可用价格的产品的输出:
+----+--------------+--------+--------------+--------------+-----------+----------+
| id | name         | active | sortSequence | creationDate | sellPrice | quantity |
+----+--------------+--------+--------------+--------------+-----------+----------+
|  1 | product_id_1 |      1 |            1 |   1287481220 |     22.00 |       10 |
+----+--------------+--------+--------------+--------------+-----------+----------+
没有定价的产品的输出
+----+------+--------+--------------+--------------+-----------+----------+
| id | name | active | sortSequence | creationDate | sellPrice | quantity |
+----+------+--------+--------------+--------------+-----------+----------+
| NULL | NULL | NULL |         NULL |         NULL |      NULL |     NULL |
+----+------+--------+--------------+--------------+-----------+----------+
所需的输出:
+----+--------------+--------+--------------+--------------+-----------+----------+
| id | name         | active | sortSequence | creationDate | sellPrice | quantity |
+----+--------------+--------+--------------+--------------+-----------+----------+
|  2 | product_id_2 |      1 |            1 |   1287481220 |     NULL  |     NULL |
+----+--------------+--------+--------------+--------------+-----------+----------+
更新资料 似乎我在选择不存在的产品!很蠢     
已邀请:
对product_price表使用LEFT OUTER JOIN怎么样?
SELECT p.*, MIN(pp.sellPrice) as sellPrice, pp.quantity FROM `product` as p
LEFT JOIN `product_price_group` as ppg ON ppg.productId = p.`id`
LEFT OUTER JOIN `product_price` as pp ON pp.priceGroupId = ppg.`id`
WHERE p.`id` = 1 AND p.`active` = 1
这是你想要的吗? 更新:修订-像其他人说的那样,
LEFT JOIN = LEFT (OUTER) JOIN
,这样在这种情况下将对您没有帮助...     
我可能不正确,但是我对LEFT JOIN的理解一直是SQL语句中编写的相等性测试中的表引用...此外,这是我如何编写查询...从表开始m期望FIRST(左),第二次连接到OTHER(右)表...还要保持该关系的连接条件...
select from x left join y where x.fld = y.fld
   instead of 
select from x left join y where y.fld = x.fld
因此,我将按以下方式调整您的查询
SELECT
      p.*, 
      MIN(pp.sellPrice) as sellPrice, 
      pp.quantity 
   FROM 
      product as p
         LEFT JOIN product_price_group as ppg 
            ON p.id = ppg.productId
            LEFT JOIN product_price as pp 
               ON ppg.id = pp.priceGroupId
   WHERE 
          p.id = 1 
      AND p.active = 1
此外,您可以用IFNULL(field,0)包装min()和数量,以防止显示NULL,而实际为零。     

要回复问题请先登录注册