SQL puzzle a or b but not both

From: Ken F <kenf_at_artesyncp.com>
Date: 25 Sep 2002 07:29:22 -0700
Message-ID: <f12fe9f8.0209250629.3c11dfea@posting.google.com>

I have three tables; Items, Item_cost, and Cost_type.

An item may have many costs of different types. Cost_type is simply a reference table e.g. there can be many instances of each cost type
(one per item).

The item cost table's primary key is a concatenation of item_id and cost_type.

There is at least one cost for each item. This is always the default
("a" in the sample data below).

My users want a report where they can specify the Cost Type to report on. But; if the specified Cost Type does not exist for an item they want to see the default cost. They do NOT want to see both.

For example (using the sample data below), if they specified Cost Type "b":

Item_Name Cost_Type Cost

ten		b		12
twenty	        a		21
thirty  	b		32

Any thoughts on SQl techniques for this ?

Thank you in advance
Ken F.

Sample data

drop table kjf_item;

create table kjf_item
(item_id number,

 item_name varchar(10));  

 Insert into kjf_item
 values (10,'ten');
  Insert into kjf_item
 values (20,'twenty');
  Insert into kjf_item
 values (30,'thirty');

---

 drop table kjf_cost_type;

create table kjf_cost_type
(type varchar(10),

type_id number );  

 Insert into kjf_cost_type
 values ('a',1);
  Insert into kjf_cost_type
 values ('b',2);
  Insert into kjf_cost_type
 values ('c',3);

---

drop table kjf_item_cost;

create table kjf_item_cost


(item_id number,

 type_id number,
 cost number(6,2));
 
insert into kjf_item_cost
values (10,1,11.00);
insert into kjf_item_cost
values (10,2,12.00);
insert into kjf_item_cost
values (20,1,21.00);

insert into kjf_item_cost
values (30,1,31.00)

insert into kjf_item_cost
values (30,2,32.00);
insert into kjf_item_cost
values (30,3,33.00);

Received on Wed Sep 25 2002 - 09:29:22 CDT