mysql - Mysql查询未在订单属性表上返回Null行

Question

我正在尝试执行一个查询，在该查询中检索属性以及来自不同表的值。我遇到的问题是它只返回order_property_values表内匹配的结果行​​。即使在表中找不到匹配项，我也希望它返回所有结果order_property_values。

这是我现在的查询。

SELECT * FROM `properties` as t1 
    LEFT JOIN `order_property_values` AS t2 
    ON t1.`id` =  t2.`property_id` 
    WHERE t1.`id` 
        IN (SELECT `property_id` FROM `assigned_properties` 
            WHERE `component_id` 
               IN (SELECT `component_id` FROM assigned_components 
                   WHERE `product_id` = (SELECT `product_id` 
                                         FROM `order_items` 
                                         WHERE `id` = 1)
                  )
            ) AND t2.`order_id` = 13

此查询以我想要的方式检索所有内容，但没有空匹配行。

IN仅返回表中可能/可能不存在的ID order_property_values。

我已经尝试过NOT IN，IS NULL但似乎也无法正常工作。

提前感谢您节省了一天。

标签： mysql

Answer 1

SELECT * FROM `properties` as t1 
    LEFT JOIN `order_property_values` AS t2 
    ON t1.`id` =  t2.`property_id` AND t2.`order_id` = 13
    WHERE t1.`id` 
        IN (SELECT `property_id` FROM `assigned_properties` 
            WHERE `component_id` 
               IN (SELECT `component_id` FROM assigned_components 
                   WHERE `product_id` = (SELECT `product_id` 
                                         FROM `order_items` 
                                         WHERE `id` = 1)
                  )
            )