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