An apple farmer has several farms and at them, numbered boxes to hold
the fruit. The boxes can be numbered 1-99. The farmer has two
tables to keep track of the fruit, one that tells what fruit the box
is supposed to carry, and the other to tell how much fruit weight the
box is carrying. Since almost every box that contains fruit contains
apples, that is the default fruit indicated by box number 0. There
isn't a box 0, but every box that is not listed for some other fruit
is assumed to be for apples. Sometimes he has one or two plum and
pear boxes. His table of boxes, then is as follows:

BOX-FRUIT TABLE:
FARM BOX FRUIT
1 0 macintosh
1 59 pear
1 60 pear
1 91 plum
1 92 plum
1 96 pear
2 0 golden_del
2 60 pear
2 71 plum
2 91 plum
2 98 pear
3 0 red_del
3 1 plum
3 2 plum
3 96 pear

So every box on farm 1 except for 59,60,91,92, and 96 will contain
macintosh if it contains fruit. The following table tells if the box
actually contains fruit by the fact that there is weight value. A box
that is designated for a fruit does not necessarily have fruit in it.
A box that has a fruit weight but is not designated with a type of
fruit actually has the 0 box fruit in it (some type of apple).

BOX WEIGHT TABLE:
FARM BOX WEIGHT
1 1 13
1 2 12
1 3 13
1 59 14
1 65 15
1 91 10
1 96 17
1 99 19
2 1 12
2 2 14
3 1 17
3 2 13
3 3 14

The question is, how do I join the box_fruit table to the box_weight
table so that the box_weight table as a forth column indicating the
fruit type?

Thanks for your help.

Greg

Greg wrote:

> An apple farmer has several farms and at them, numbered boxes to hold
> the fruit. The boxes can be numbered 1-99. The farmer has two
> tables to keep track of the fruit, one that tells what fruit the box
> is supposed to carry, and the other to tell how much fruit weight the
> box is carrying. Since almost every box that contains fruit contains
> apples, that is the default fruit indicated by box number 0. There
> isn't a box 0, but every box that is not listed for some other fruit
> is assumed to be for apples. Sometimes he has one or two plum and
> pear boxes. His table of boxes, then is as follows:
>
> BOX-FRUIT TABLE:
> FARM BOX FRUIT
> 1 0 macintosh
> 1 59 pear
> 1 60 pear
> 1 91 plum
> 1 92 plum
> 1 96 pear
> 2 0 golden_del
> 2 60 pear
> 2 71 plum
> 2 91 plum
> 2 98 pear
> 3 0 red_del
> 3 1 plum
> 3 2 plum
> 3 96 pear
>
> So every box on farm 1 except for 59,60,91,92, and 96 will contain
> macintosh if it contains fruit. The following table tells if the box
> actually contains fruit by the fact that there is weight value. A box
> that is designated for a fruit does not necessarily have fruit in it.
> A box that has a fruit weight but is not designated with a type of
> fruit actually has the 0 box fruit in it (some type of apple).
>
> BOX WEIGHT TABLE:
> FARM BOX WEIGHT
> 1 1 13
> 1 2 12
> 1 3 13
> 1 59 14
> 1 65 15
> 1 91 10
> 1 96 17
> 1 99 19
> 2 1 12
> 2 2 14
> 3 1 17
> 3 2 13
> 3 3 14
>
> The question is, how do I join the box_fruit table to the box_weight
> table so that the box_weight table as a forth column indicating the
> fruit type?

Have a look at OUTER JOINs.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

I think Greg's essential question is how to realize the requirement
which is
"every box that is not listed(in box_fruit) for some other fruit is
assumed to be for apple(i.e. BOX 0)".
Note: the phrases in parentheses are added by me.

If so, following may be an answer.
------------------------- Commands Entered -------------------------
SELECT F.FARM, F.BOX
, CASE F.BOX WHEN 0 THEN 'apple' ELSE FRUIT END AS fruit
, COALESCE(WEIGHT,0) AS weight
FROM box_fruit F
LEFT OUTER JOIN
LATERAL
(SELECT SUM(WEIGHT)
FROM box_weight W
WHERE F.FARM = W.FARM
AND
( F.BOX = W.BOX
OR
F.BOX = 0
AND
NOT EXISTS
(SELECT *
FROM box_fruit FNE
WHERE FNE.FARM = W.FARM
AND FNE.BOX = W.BOX
)
)
) W(WEIGHT)
ON 0=0
;
--------------------------------------------------------------------

FARM BOX FRUIT WEIGHT
----------- ----------- --------------- -----------
1 0 apple 72
1 59 pear 14
1 60 pear 0
1 91 plum 10
1 92 plum 0
1 96 pear 17
2 0 apple 26
2 60 pear 0
2 71 plum 0
2 91 plum 0
2 98 pear 0
3 0 apple 14
3 1 plum 17
3 2 plum 13
3 96 pear 0

15 record(s) selected.

I'm looking for the query that will produce the following result:

FARM BOX WEIGHT FRUIT
1 1 13 apple (because the box 1 fruit was not
explicitly listed and the default fruit (box 0) is apple)
1 2 12 apple "
1 3 13 apple "
1 59 14 pear (because box 59 was explicitly
listed as pear)
1 65 15 apple
1 91 10 plum
1 96 17 pear
1 99 19 apple
2 1 12 apple
2 2 14 apple
3 1 17 plum
3 2 13 plum
3 3 14 apple

It is a most basic and simple LEFT OUTER JOIN, if you know the builtin
function COALESCE.
------------------------- Commands Entered -------------------------
SELECT W.FARM, W.BOX, WEIGHT
, COALESCE(FRUIT, 'apple') AS FRUIT
FROM box_weight W
LEFT OUTER JOIN
box_fruit F
ON F.FARM = W.FARM
AND F.BOX = W.BOX
ORDER BY
W.FARM, W.BOX;
--------------------------------------------------------------------

FARM BOX WEIGHT FRUIT
----------- ----------- ----------- ---------------
1 1 13 apple
1 2 12 apple
1 3 13 apple
1 59 14 pear
1 65 15 apple
1 91 10 plum
1 96 17 pear
1 99 19 apple
2 1 12 apple
2 2 14 apple
3 1 17 plum
3 2 13 plum
3 3 14 apple

13 record(s) selected.

Thank you so much for your help so far.

What if the box 0 fruit for a particular farm is not apple?

BOX-FRUIT TABLE:
FARM BOX FRUIT
1 0 apple
1 59 pear
1 60 pear
1 91 plum
1 92 plum
1 96 pear
2 0 apple
2 60 pear
2 71 plum
2 91 plum
2 98 pear
3 0 pear <=====
3 1 plum
3 2 plum
3 96 apple

same box-wieght table

RESULT:
FARM BOX WEIGHT FRUIT
----------- ----------- ----------- ---------------
1 1 13 apple
1 2 12 apple
1 3 13 apple
1 59 14 pear
1 65 15 apple
1 91 10 plum
1 96 17 pear
1 99 19 apple
2 1 12 apple
2 2 14 apple
3 1 17 plum
3 2 13 plum
3 3 14 pear (not apple since box0
fruit is pear for this farm)

COALESCE(FRUIT, 'apple') would have to change to the equivalent of
COALESCE(FRUIT, {whatever box0 fruit is for this farm})

Greg

>> The question is, how do I join the fruit boxes table to the box weight table so that the box weight table as a forth column indicating the fruit type? <<

This design flaw is common enough to have a name --Attribute
Splitting. What are the attributes of a fruit box? Are they all in
one table or are they split across two or more tables (so you have to
do complex joins for the most basic facts)? You already know the
answer to that one!

Try a relational design, with constraints, etc. Why did you use
singular names for tables which model sets of things? Your fruits
seem to be labeled with the type and the variety of the produce, so
let's code that as two columns.

CREATE TABLE FruitBoxes
(farm_nbr INTEGER NOT NULL,
box_nbr INTEGER NOT NULL
CHECK (box_nbr BETWEEN 1 AND 99),
PRIMARY KEY (farm_nbr, box_nbr),
box_wgt INTEGER DEFAULT 0 NOT NULL
CHECK (box_wgt >= 0),
fruit_type CHAR(5) DEFAULT 'Apple' NOT NULL,
fruit_variety CHAR(10) DEFAULT '{{unk}}' NOT NULL,
FOREIGN KEY (fruit_type, fruit_variety)
REFERENCES Fruits (fruit_type, fruit_variety)
..);

CREATE TABLE Fruits
(fruit_type CHAR() NOT NULL,
fruit_variety CHAR(10) NOT NULL,
PRIMARY KEY (fruit_type, fruit_variety),
..);

INSERT INTO Fruits
VALUES ('Apple', 'Macintosh', ..),
('Apple', 'Golden_Del', ..),
('Apple', 'Red_Del', ..),
('Apple', '{{unk}}', ..), -- unknown generic apple code
('Pear', 'Bartlett', ..), ..;


Now let's assure that all the boxes are inventoried with an
assertion.

CREATE ASSERTION AllBoxesInventoried
AS
CHECK (99 = ALL (SELECT COUNT(*)
FROM FruitBoxes
GROUP BY farm_nbr));

If you are worried about the extra storage that a proper design
requires, consider that you can get megabytes for pennies, but the
execution time you will waste, the complexity of maintaining lateral
outer joins, lack of data integrity, etc. in what you have now will
cost you dollars in human time and errors.