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)