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
Does
solves your issue?
You need a left outer join on the product_custom_fields like this