How to know if two intervals coincide at some point? I have two tables that store two intervals where the values mean meters. The first interval corresponds to geological codes (VBv, P4, etc). The second interval corresponds to samples. They are connected through a field called Hole ID. I need all the sample ranges that match the code ranges, putting

Two intervals coincide at some point in SQL-Server

How to know if two intervals coincide at some point? I have two tables that store two intervals where the values mean meters.

The first interval corresponds to geological codes (VBv, P4, etc).
The second interval corresponds to samples.

They are connected through a field called Hole ID.

CREATE TABLE codes (
  code VARCHAR (10), 
  depth_from INT, 
  depth_to INT,
  hole_id INT
);

INSERT INTO codes VALUES ('P4', 1, 2, 100);
INSERT INTO codes VALUES ('VBv', 2, 6, 100);
INSERT INTO codes VALUES ('P4', 6, 10, 100);

CREATE TABLE samples (
  sample VARCHAR (50), 
  depth_from INT, 
  depth_to INT,
  hole_id INT
);

INSERT INTO samples VALUES ('OP0051780', 1, 3, 100);
INSERT INTO samples VALUES ('OP0051781', 3, 9, 100);
INSERT INTO samples VALUES ('OP0051780', 9, 10, 100);

I need all the sample ranges that match the code ranges, putting a certain code as a parameter.

What I have tried: I built a query that checks if the “from” or “to” match. I also check if any interval is contained in another.

SELECT * FROM codes INNER JOIN samples ON codes.hole_id = samples.hole_id
WHERE codes.code = 'VBv' AND

(
-- Possibility 1: From or to match
(samples.depth_from = codes.depth_from or samples.depth_to = codes.depth_to)

-- Possibility 2: Some interval contained in another.
or (samples.depth_from >= codes.depth_from and samples.depth_to <= codes.depth_to)
or (codes.depth_from >= samples.depth_from and codes.depth_to <= samples.depth_to)

)

This works for the following situations:

But when there is no match in the “from” and “to” and one interval is not contained in the other, I don’t know how to solve it.

Answer

SELECT * FROM codes INNER JOIN samples ON codes.hole_id = samples.hole_id
WHERE codes.code = 'VBv' AND
(samples.depth_from <= codes.depth_to AND samples.depth_to >= codes.depth_from)

Advertisement

Answer