skip to Main Content

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

2

Answers


  1. 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?

    Login or Signup to reply.
  2. 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)
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search