From: Chad Wagner <chad.wagner@gmail.com>
To: devil live <definite_ocean@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Conditional SQL Query
Date: Thu, 11 Jan 2007 17:03:32 -0500
Message-ID: <81961ff50701111403t443e47f6rd1fa27b50751f5b7@mail.gmail.com> (raw)
In-Reply-To: <BAY20-F159C48AE0E201B150535BFEEB10@phx.gbl>
References: <BAY20-F159C48AE0E201B150535BFEEB10@phx.gbl>
On 1/11/07, devil live <definite_ocean@hotmail.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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: chad.wagner@gmail.com, definite_ocean@hotmail.com
Subject: Re: Conditional SQL Query
In-Reply-To: <81961ff50701111403t443e47f6rd1fa27b50751f5b7@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox