Mysql Query Not returning Null rows on order property table

Question

I'm trying to execute a query in which it retrieves the properties, and the values from a different table. The problem I am having is that it's only returning result rows that match inside the order_property_values table. I want it to return all results even if it can't find a match inside the order_property_values table.

Here's the query that I have now.

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

This query retrieves everything the way I want, but without the null matched rows.

The IN is returning only the ID's that May/MayNot be present inside the order_property_values table.

I've tried NOT IN and also IS NULL but can't seem to get this functioning.

Thanks in advance for saving the day.

I'm retrieving a group of component ID's, and a group of property ID's. I'm not sure how that would work. — joeb, Jul 05 '18 at 17:18

score 1 · Accepted Answer · answered Jul 05 '18 at 17:16

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

Mysql Query Not returning Null rows on order property table

1 Answers1