Re: Conditional SQL Query

On 1/11/07, devil live <definite_ocean(at)hotmail(dot)com> wrote:
>
> 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;

--
Chad
http://www.postgresqlforums.com/