Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 446649FB1C2
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 11 Jan 2007 18:03:43 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024)
	with ESMTP id 90847-09 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 11 Jan 2007 18:03:33 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from nf-out-0910.google.com (nf-out-0910.google.com
 [64.233.182.186])
	by postgresql.org (Postfix) with ESMTP id C252A9FA59C
	for <pgsql-sql@postgresql.org>; Thu, 11 Jan 2007 18:03:36 -0400 (AST)
Received: by nf-out-0910.google.com with SMTP id x4so1047716nfb
	for <pgsql-sql@postgresql.org>; Thu, 11 Jan 2007 14:03:32 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=cYEFfV7/xi1hWG9v3j/WD/upPOBlyMnR/Bw1uO8AiXK4pZYzpuf+t4jBu4l5LgwsTIgY5C8v8Wn9oqxB9NsqMIIR8Xcr26EmDnfWy//ilezmmGgSFPgUniq4SvwcO62ARj2kBJyKByce+rIZjoxEOS+ezomv0ktP/BDzQ0B1lAE=
Received: by 10.49.13.14 with SMTP id q14mr2547446nfi.1168553012744;
	Thu, 11 Jan 2007 14:03:32 -0800 (PST)
Received: by 10.48.230.1 with HTTP; Thu, 11 Jan 2007 14:03:32 -0800 (PST)
Message-ID: <81961ff50701111403t443e47f6rd1fa27b50751f5b7@mail.gmail.com>
Date: Thu, 11 Jan 2007 17:03:32 -0500
From: "Chad Wagner" <chad.wagner@gmail.com>
To: "devil live" <definite_ocean@hotmail.com>
Subject: Re: Conditional SQL Query
Cc: pgsql-sql@postgresql.org
In-Reply-To: <BAY20-F159C48AE0E201B150535BFEEB10@phx.gbl>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_52161_15837435.1168553012693"
References: <BAY20-F159C48AE0E201B150535BFEEB10@phx.gbl>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/36
X-Sequence-Number: 27188

------=_Part_52161_15837435.1168553012693
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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/

------=_Part_52161_15837435.1168553012693
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<br><br><div><span class="gmail_quote">On 1/11/07, <b class="gmail_sendername">devil live</b> &lt;<a href="mailto:definite_ocean@hotmail.com">definite_ocean@hotmail.com</a>&gt; wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
NOW : product_tree_template table is the default table for production<br>ingredients of the PRODUCT<br><br>but sometimes my customer using special product_tree table for some<br>production_no<br><br>how can I write a query to get right ingredients of a product basis on
<br>production_no field<br><br>such as;<br><br>first check production_no if product_tree_special table if not found then<br>look at template table...<br><br>What are your suggestions?</blockquote><div><br><br>It would be helpful if you could show the create table&#39;s, sample data, and expected results for each case.
<br><br>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.<br><br>One of the ways to achieve this is to outer join your &quot;optional&quot; data table, and use the CASE (or perhaps NULLIF) to conditionally swap the result columns from the optional or default data tables.&nbsp; For example:
<br><br>select p.product_code<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,pt.stock_code_to_make_product<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,case when ps.product_code is not null and ps.stock_code_to_make_product is not null<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; then ps.amount<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else 
pt.amount<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end AS amount<br>&nbsp; from product p<br>&nbsp; join product_tree_template pt on p.product_code = pt.product_code<br>&nbsp; left join product_tree_special ps on pt.product_code = ps.product_code<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and 
pt.stock_code_to_make_product = ps.stock_code_to_make_product;</div><br></div><br clear="all"><br>-- <br>Chad<br><a href="http://www.postgresqlforums.com/">http://www.postgresqlforums.com/</a>

------=_Part_52161_15837435.1168553012693--