Re: SQL question

Hi!

Bernhard Nemec wrote:
>
> My database contains information about "samples" that are stored in
> "racks". Every "rack" has a capacity ("size"). The positions in a rack
> are numbered. A rack does not have to be filled in sequentially, i. e.
> there may be a sample at position 3 and one at position 11 while the
> rest of the rack remains empty.
> I have the following tables:
>
> SAMPLES(
> ID:INTEGER NOT NULL,
> RACK:INTEGER NOT NULL,
> POSITION_IN_RACK: INTEGER NOT NULL
> );
>
> RACKS(
> ID:INTEGER NOT NULL,
> SIZE:INTEGER NOT NULL
> );
>
> My question: how can I retrieve a list of all free positions in a
> certain rack? I want to write an sql query with a resulting table of the
> form
>
> FREEPOSITIONS(
> RACK:INTEGER,
> POSITION:INTEGER
> );

One solution would be to have a helper table that contains one row for every possible position:

HELPER(
POSITION: INTEGER NOT NULL
);

This table has to be filled with the values 1 to max(SIZE). Then you can formulate the following query:

SELECT Rack.ID, Helper.Position
 FROM Rack, Helper
 WHERE Helper.Position <= Rack.Size AND Helper.Position NOT IN

       (SELECT Position_in_rack
        FROM Samples
        WHERE Samples.Rack = Rack.ID);

hth
Heinz Received on Wed May 10 2000 - 00:00:00 CEST