Re: Box query

Try a slightly different approach. Begin with one dimension and stronger DDL:

CREATE TABLE Boxes
(box_id CHAR (1) NOT NULL,
 dim CHAR(1) NOT NULL,
 PRIMARY KEY (box_id, dim),
 low INTEGER NOT NULL,
 high INTEGER NOT NULL,
 CHECK (low < high));

INSERT INTO Boxes VALUES ('A', 'x', 0, 2);
INSERT INTO Boxes VALUES ('B', 'x', 1, 3);
INSERT INTO Boxes VALUES ('C', 'x', 10, 12);

INSERT INTO Boxes VALUES ('A', 'y', 0, 2);
INSERT INTO Boxes VALUES ('B', 'y', 1, 3);
INSERT INTO Boxes VALUES ('C', 'y', 0, 4);

--in 2 dimension: first shot:
SELECT B1.box_id, B2.box_id, B1.dim
  FROM Boxes AS B1, Boxes AS B2
 WHERE B1.box_id < B2.box_id
   AND B1.dim = B2.dim
   AND (B1.high - B1.low) + (B2.high - B2.low)

> ABS(B1.high - B2.low);

Now look for a common area in (x,y) by having overlaps in both dimensions:

SELECT B1.box_id, B2.box_id
  FROM Boxes AS B1, Boxes AS B2
 WHERE B1.box_id < B2.box_id
   AND B1.dim = B2.dim
   AND (B1.high - B1.low) + (B2.high - B2.low)

> ABS(B1.high - B2.low)

 GROUP BY B1.box_id, B2.box_id
 HAVING COUNT(B1.dim) = 2;

--3 dimensions:

INSERT INTO Boxes VALUES ('A', 'z', 0, 2);
INSERT INTO Boxes VALUES ('B', 'z', 1, 3);
INSERT INTO Boxes VALUES ('C', 'z', 0, 100);

Now change the HAVING clause to COUNT(B1.dim) = 3 or (SELECT COUNT (DISTINCT dim) FROM Boxes) if you do not know the space you are using. Received on Sun Apr 23 2006 - 21:55:42 CEST