Thread: Conditional SQL Query
I want to write sql statement about product, product_tree_special, product_tree_template product has product_code varchar(20) PRIMARY KEY, product_name varchar(20) product_tree_template has product_code varchar(20) PK, stock_code_to_make_product(20) PK, amout float4 product_tree_spcial has production_no serial, product_code varchar(20) PK, stock_code_to_make_product(20) PK, amout float4 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? PS: I do not wanna write PL functions for this... Thanks _________________________________________________________________ Hava durumunu bizden �grenin ve evden �yle �ikin! http://www.msn.com.tr/havadurumu/
I think that the simplest solution is to use an union e.g: 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 1 = 1 AND PRD.product_code = PTT.product_code 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 1 = 1 AND PRD.product_code = PTS.product_code ORDER BY 1,2,4 I've added an order by the columns 1,2 and 4 (just guessing), note that this particular solution uses positional ordering, i.e no column names. Mario devil live wrote: > > I want to write sql statement about product, product_tree_special, > product_tree_template > > product has > product_code varchar(20) PRIMARY KEY, > product_name varchar(20) > > product_tree_template has > product_code varchar(20) PK, > stock_code_to_make_product(20) PK, > amout float4 > > product_tree_spcial has > production_no serial, > product_code varchar(20) PK, > stock_code_to_make_product(20) PK, > amout float4 > > > > 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? > > > > PS: I do not wanna write PL functions for this... > > Thanks > > _________________________________________________________________ > Hava durumunu bizden ögrenin ve evden öyle çikin! > http://www.msn.com.tr/havadurumu/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
On 1/11/07, devil live <definite_ocean@hotmail.com> wrote:
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;
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/
M.P.Dankoor wrote:> devil live wrote:>> how can I write a query to get right ingredients of a product basis on>>
production_nofield>>>> such as;>>>> first check production_no if product_tree_special table if not found>> then look at
templatetable...>>>> 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 = casewhen (select production_no from product_tree_special ts where
ts.product_code=PRD.product_code)is nullthen 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 = casewhen (select production_no from product_tree_special ts where
ts.product_code=PRD.product_code)is not nullthen 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