I am trying to join two QVD tables based on a field in a third table (since the primary key in the first table will not have a match in the third table for products that are refunded). The field in the third table contains order_type field which is the base for my conditional join. Is there a way to have a conditional join in the load script to make this possible?
Tables:
1. order_products
2. order_product_inventory
3. orders
Below are the two conditions:
#1. if orders.order_type <> 'refund' join order_products.id to order_product_inventory.order_product_id
#2. if orders.order_type = 'refund' join order_products.refunded_order_product_id to order_product_inventory.order_product_id
If you'd like to see the sql query for the two conditions, see below:
#1 order_type <> 'refund'
Select *
FROM orders ord
LEFT JOIN order_products opr on ord.id = opr.order_id
LEFT JOIN order_product_inv opi on opr.id = opi.order_product_id
WHERE ord.order_type <> 'refund'
#2 order_type = 'refund'
Select *
FROM orders ord
LEFT JOIN order_products opr on ord.id = opr.order_id
LEFT JOIN order_product_inventory opi on opr.refunded_order_product_id = opi.order_product_id
WHERE ord.order_type = 'refund'
