Finding interval overlap between two tables I have two tables that have top_depth, bottom_depth, lith_type.  One defines the different layers found in a borehole and the other define where samples are extracted in each of the layers.  

I would like to find all samples that intersects the layer boundaries. Although, I was able to solve my problem, I was hoping there was a more elegant solution.  This is what I did:

CREATE TABLE lithology ( official_name TEXT, top_depth FLOAT, bottom_depth FLOAT, lith_type TEXT );
INSERT INTO lithology VALUES('B1', 0, 3,'SC'); 
INSERT INTO lithology VALUES('B1', 3, 11,'SS');
INSERT INTO lithology VALUES('B1', 11, 20,'STS');
INSERT INTO lithology VALUES('B1', 1, 21, 'SS');

CREATE TABLE sample_data ( official_name TEXT, sample_id TEXT, top_depth FLOAT, bottom_depth FLOAT, lith_type TEXT );
INSERT INTO sample_data VALUES('B1', 1, 1, 2,'SC');
INSERT INTO sample_data VALUES('B1', 2, 3, 4,'SS');
INSERT INTO sample_data VALUES('B1', 3, 6, 7,'SS');
INSERT INTO sample_data VALUES('B1', 4, 10.5, 11.5,'SS');
INSERT INTO sample_data VALUES('B1', 5, 14, 15,'SS');
INSERT INTO sample_data VALUES('B1', 6, 19, 20,'SS');

select 
    L.official_name, S.sample_id,
    L.top_depth, L.bottom_depth, L.lith_type,
    S.top_depth, S.bottom_depth, S.lith_type
from
    lithology L, sample_data S
where
    L.official_name = S.official_name 
and
    (S.bottom_depth &gt; L.top_depth and L.top_depth &gt; S.top_depth or
    S.bottom_depth &gt; L.bottom_depth and L.bottom_depth &gt; S.top_depth or
    S.top_depth &lt; L.top_depth and S.bottom_depth &gt; L.bottom_depth )
order by L.official_name,S.sample_id


This should show that sample_id=4 is the sample to check.
The simplest way to work on this is to follow Martin Fowler's (and many others as well) technique: http://c2.com/cgi/wiki?TestIfDateRangesOverlap

Note: SQL Server syntax

create table Layers (layer_top_depth int, layer_bottom_depth int, layer_lith_type int);
create table Samples (sample_top_depth int, sample_bottom_depth int, sample_lith_type int);
go
insert into layers  values(4, 7, 1) ; 
insert into layers  values(6, 8, 2);
insert into layers  values(5, 6, 3);
insert into Samples values(6, 7, 1);
insert into Samples values(6, 7, 2);
insert into Samples values(3, 8, 3);
go
SELECT L.layer_top_depth, L.layer_bottom_depth, L.layer_lith_type, 
       S.sample_top_depth, S.sample_bottom_depth, S.sample_lith_type
 FROM Layers L
   JOIN Samples S ON
       (L.layer_top_depth &lt;= s.sample_bottom_depth AND
        S.sample_top_depth &lt;= L.layer_bottom_depth);
go
drop table Layers;
drop table samples;


If the samples at boundary layer should not be reported, replace the comparison of &lt;= with &lt; as mentioned by ypercubeᵀᴹ and is shown below:

SELECT L.layer_top_depth, L.layer_bottom_depth, L.layer_lith_type, 
       S.sample_top_depth, S.sample_bottom_depth, S.sample_lith_type
 FROM Layers L
   JOIN Samples S ON
       (L.layer_top_depth &lt; s.sample_bottom_depth AND
        S.sample_top_depth &lt; L.layer_bottom_depth);


The lyth_type is just riding along in this since you did not mention how to use it.