Friday, January 25, 2013 9:28 AM
i have a query like this;
SELECT order_product.order_product_id, order_product.order_id, order_product.product_id, order_product.name, order_product.model, order_product.quantity,
order_product.price, order_product.total, order_product.tax, product.tax_class_id AS TaxClass_ID, tax_rule.tax_rate_id, tax_rate.rate, case when tax_rate.rate = 10 then order_product.tax as tax1
FROM order_product, product, tax_rule, tax_rate
WHERE (product.tax_class_id = tax_rule.tax_class_id) AND (order_product.product_id = product.product_id) AND (tax_rule.tax_rate_id = tax_rate.tax_rate_id)
GROUP BY order_product_id
ORDER BY order_id DESC
i have 2 different tax rate in tax_rate.rate column , i want to check if tax rate is 10 than put the value of order_product.tax value as tax1 and and if tax_rate.rate is 2 than put the value of order_product.tax value as tax2
i have tried case when but i dont know how to make correct it.
Friday, January 25, 2013 9:47 AM
E.g. using INNER JOIN and table alias names for better readability:
SELECT OP.order_product_id , OP.order_id , OP.product_id , OP.name , OP.model , OP.quantity , OP.price , OP.total , OP.tax , P.tax_class_id AS TaxClass_ID , TRL.tax_rate_id , TRT.rate , CASE WHEN TRT.rate = 10 THEN OP.tax ELSE NULL END AS tax1 , CASE WHEN TRT.rate = 2 THEN OP.tax ELSE NULL END AS tax2 FROM order_product OP INNER JOIN product P ON OP.product_id = P.product_id INNER JOIN tax_rule TRL ON P.tax_class_id = TRL.tax_class_id INNER JOIN tax_rate TRT ON TRL.tax_rate_id = TRT.tax_rate_id ORDER BY OP.order_id DESC;
btw, change the type of this thread to question, please.
Friday, January 25, 2013 10:21 AM