Re: SQL puzzle a or b but not both

Ken F wrote:

> 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);

One thought ... and one alone ... do I get the credit for doing your homework for you?

Daniel Morgan Received on Wed Sep 25 2002 - 11:12:17 CDT