Message-ID: <45A6C9BD.3020002@007Marketing.com>
Date: Fri, 12 Jan 2007 10:05:25 +1030
From: Shane Ambler <pgsql@007Marketing.com>
User-Agent: Thunderbird 1.5.0.8 (Macintosh/20061025)
MIME-Version: 1.0
To: "M.P.Dankoor" <m.p.dankoor@gmail.com>
CC: devil live <definite_ocean@hotmail.com>,  pgsql-sql@postgresql.org
Subject: Re: Conditional SQL Query
References: <BAY20-F159C48AE0E201B150535BFEEB10@phx.gbl>
	<45A6B3C6.7000707@gmail.com>
In-Reply-To: <45A6B3C6.7000707@gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

M.P.Dankoor wrote:
 > devil live wrote:
 >> 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?
 >>

I think M.P. Dankoor's suggestion is close but I believe the conditional 
part you are looking for would make it into this -

select  PRD.product_code
       ,PRD.product_name
       ,NULL::int            AS production_no
       ,PTT.stock_code_to_make_product
       ,PTT.amount
from  product PRD
     ,product_tree_template PTT
where PRD.product_code='searchcode'
AND PRD.product_code = case
	when (select production_no from product_tree_special ts
		where ts.product_code=PRD.product_code) is null
	then PTT.product_code else '' end
UNION
select  PRD.product_code
       ,PRD.product_name
       ,PTS.production_no
       ,PTS.stock_code_to_make_product
       ,PTS.amount
from  product PRD
     ,product_tree_special  PTS
where PRD.product_code='searchcode'
AND PRD.product_code = case
	when (select production_no from product_tree_special ts
		where ts.product_code=PRD.product_code) is not null
	then PTS.product_code else '' end
ORDER BY 1,2,4


So if we entered the following -

INSERT INTO product VALUES ('one','test one');
INSERT INTO product VALUES ('two','test two');
INSERT INTO product_tree_special VALUES (1,'one','special list',1.1);
INSERT INTO product_tree_template VALUES ('two','template parts',2.2);


change both WHERE clauses to PRD.product_code='two' you will get -

two	test two	<null>	template parts	2.2	

then change both WHERE clauses to PRD.product_code='one' you will get -

one	test one	1	special list	1.1	


-- 

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz