MySQL Show results where not all conditions are met-CodePudding

I have three tables

Table A (orders)

 order_number     order_id
     9999            123

Table B (order_items)

order_id    product_id    price
   123         111          10
   123         112          11
   123         113          12
   123         114          13

and Table C (product_customfields)

product_id    customfield_id      customfield_value
  111              10                   A
  112              10                   A
  113              10                   B
  113              9                   xyz

As a result I would like to get the product_id the price and in case a product has the customfield_id = 10 also the customfield_value

So in this case as a result I expect:

product_id   price    customfield_value
    111       10            A
    112       11            A
    113       12            B
    114       13          (empty)

In general my query looks like the following:

select B.product_id, B.price, C.customfield_value from orders A 
left join order_items B on A.order_id=B.order_id 
left join product_customfields C on B.product_id=C.product_id where A.order_number=9999 and C.customfield_id = 10

Of course the result will not show the product_id 114 because it has no customfield_id assigned in the database table with a value of "10"

Nevertheless could someone point me in the right direction how to build the query in a way to also show all products of the orders also if they are not assigned to a condition in the table.

Thank you

CodePudding user response：

Does

select B.product_id, B.price, C.customfield_value from orders A 
left join order_items B on A.order_id=B.order_id 
left join product_customfields C on B.product_id=C.product_id 
where A.order_number=9999 and (C.customfield_id = 10 or C.customfield_id IS NULL)

solves your issue?

CodePudding user response：

You need a left outer join on the product_custom_fields like this

select B.product_id, B.price, C.customfield_value 
from orders A 
left join order_items B 
     on A.order_id=B.order_id 
left outer join product_customfields C 
     on B.product_id=C.product_id 
where A.order_number=9999 and 
      (C.customfield_id = 10 or C.customfield_id IS NULL)