NOW : product_tree_template table is the default table for production ingredients of the PRODUCT
but sometimes my customer using special product_tree table for some production_no
how can I write a query to get right ingredients of a product basis on production_no field
such as;
first check production_no if product_tree_special table if not found then look at template table...
What are your suggestions?
It would be helpful if you could show the create table's, sample data, and expected results for each case.
One thing to point out here is you should define your constraints, if a column is not meant to be nullable then it should be NOT NULL.
One of the ways to achieve this is to outer join your "optional" data table, and use the CASE (or perhaps NULLIF) to conditionally swap the result columns from the optional or default data tables. For example:
select p.product_code ,pt.stock_code_to_make_product ,case when ps.product_code is not null and ps.stock_code_to_make_product is not null then ps.amount else pt.amount end AS amount from product p join product_tree_template pt on p.product_code = pt.product_code left join product_tree_special ps on pt.product_code = ps.product_code and pt.stock_code_to_make_product = ps.stock_code_to_make_product;
