Re: SQL question

From: Joe Celko <71062.1056_at_compuserve.com>
Date: 2000/05/13
Message-ID: <8fjvog$13u$1_at_nnrp1.deja.com>#1/1

>> 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. <<

Let me clean up your tables and write them in SQL instead of your personal pseudo-code:

CREATE TABLE Samples
(sample_id INTEGER NOT NULL PRIMARY KEY,  rack_nbr INTEGER NOT NULL

          REFERENCES Racks(rack_nbr),
 position_in_rack INTEGER NOT NULL

                  CHECK (position_in_rack > 0));

CREATE TABLE Racks
(rack_nbr INTEGER NOT NULL PRIMARY KEY,
 size INTEGER NOT NULL

      CHECK (size > 0));

>> My question: how can I retrieve a list of all free positions in a
certain rack? <<

Sure, this is the "gaps" problem. But you have to make one of two changes:

1. give up the idea of one row per rack position and get the start and stop rack positions of the gaps

SELECT R1.rack_nbr,

       S1.position_in_rack AS gap_start,
       MIN(S2.position_in_rack - 1) AS gap_end

  FROM Racks AS R1, Samples AS S1, Samples AS S2

 WHERE R1.rack_nbr = S1.rack_nbr
   AND R1.rack_nbr = S2.rack_nbr
   AND S1.rack_nbr <= S2.rack_nbr
   AND S1.position_in_rack + 1
       BETWEEN 1 AND R1.size
   AND S2.position_in_rack - 1
       BETWEEN 1 AND R1.size
   AND S1.position_in_rack + 1
       NOT IN (SELECT position_in_rack
                 FROM Samples AS S3
                WHERE S3.rack_nbr = S1.rack_nbr)
   AND S1.position_in_rack - 1
       NOT IN (SELECT position_in_rack
                 FROM Samples AS S4
                WHERE S4.rack_nbr = S1.rack_nbr)

 GROUP BY R1.rack_nbr, S1.position_in_rack;

I just got home and have not checked this code, so be careful. I have this problem in SQL FOR SMARTIES.

2) Build an auxiliary table of sequential numbers to find the missing numbers; this is more of what you wanted, I think.

CREATE TABLE Sequence (i INTEGER NOT NULL PRIMARY);  INSERT INTO Sequence VALUES (1), (2), ... , (n);

SELECT R1.rack_nbr, Q1.i AS missing_position   FROM Racks AS R1
 WHERE NOT EXISTS

       (SELECT *
          FROM Samples AS S1, Sequence AS Q1
         WHERE Q1.i = S1.position_in_rack
           AND Q1.i <= R1.size
           AND S1.rack_nbr = R1.rack_nbr);

--CELKO--
Joe Celko, SQL and Database Consultant

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat May 13 2000 - 00:00:00 CEST