Re: SQL question

Bernhard Nemec <nemec_at_mem.unibe.ch> wrote in message news:3917D70A.E382026D_at_mem.unibe.ch...
> 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
> );
>

Since no rows actually stores the empty positions, there are no way to retrieve the emptry positions with standard SQL. You could however easily do it in a general purpose programming language with a connection (ODBC) to the database.

Another solution would be to insert a defined EMPTY_SAMPLE in the empty positions. The empty-sample could be the one with ID = 0. This works if the primary key of SAMPLES is (id, rack, position). This requires that each time you create a new rack, you must insert the rows (0, rack_id, 1.. rack.size) into the SAMPLES table.

Now you can easily retrieve all empty slots in a rack:

SELECT r.ID, s.POSITION_IN_RACK
FROM samples s, racks r
WHERE r.ID = s.RACK

    AND s.ID = 0

--

Thomas