Finding interval overlap between two tables

sqlite

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 > L.top_depth and L.top_depth > S.top_depth or
    S.bottom_depth > L.bottom_depth and L.bottom_depth > S.top_depth or
    S.top_depth < L.top_depth and S.bottom_depth > L.bottom_depth )
order by L.official_name,S.sample_id

This should show that sample_id=4 is the sample to check.

Best Answer

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 <= s.sample_bottom_depth AND
        S.sample_top_depth <= 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 <= with < 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 < s.sample_bottom_depth AND
        S.sample_top_depth < L.layer_bottom_depth);

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

Related Solutions

Join for between multiple tables without common fields

Which make of SQL are you using? I am most familiar with Microsoft SQL Server. Syntactically, you need to move the WHERE after all of the JOIN statements. (I appended the tablename L to its references for consistency.)

select * from L
left outer join ER on ER.childid=L.id
left outer join E on E.id=ER.parentid
left outer join M on M.id=ER.parentid
where L.xpos > 5

This is legal syntax. The next question is what results you want. I think possibly so, although you did not show a sample of results. Perhaps you can play with the data a bit to decide what you need.

CREATE table E (id INT, title varchar(50), [datetime] DateTime);
CREATE table L (id INT, place varchar(50), xpos float, ypos float);
CREATE table M (id INT, title varchar(50), [datetime] DateTime, contact varchar(50), agenda varchar(50));
CREATE table ER (parentid INT, childid INT, parenttype INT, childtype INT);

INSERT INTO L VALUES (1, 'Boston',123.234, 23.234);
INSERT INTO L VALUES (3, 'Miami', 456.234, 78.234);
INSERT INTO ER VALUES (100,1, 5, 4);
INSERT INTO ER VALUES (200,2, 5, 4);
INSERT INTO ER VALUES (200,3, 5, 4);
INSERT INTO ER VALUES (100,4, 5, 4);
INSERT INTO E VALUES (100, 'Able','2014/01/02');
INSERT INTO E VALUES (2, 'Baker','2014/01/11');
INSERT INTO M VALUES (100, 'Love', '2014/04/04', 'George', 'None');
INSERT INTO M VALUES (200, 'Fury', '2014/05/05', 'Bernard', 'Something big');


SELECT * FROM L 
    LEFT OUTER JOIN ER on ER.childid = L.id
    LEFT OUTER JOIN E on E.id= ER.parentid
    LEFT OUTER JOIN M on M.id= ER.parentid
    WHERE L.xpos > 5

DROP TABLE E
DROP TABLE L
DROP TABLE M
DROP TABLE ER

Finding records not present across 3 tables – Sqlite3

"Find all the Users of a particular workspace that are not associated with a specific task"

You need to move the AND user_task.task_id=? condition from the WHERE clause to the related ON.

(I think you also have a typo. ON user_task.task_id = Task._id should be ON user_task.user_id = User._id):

SELECT User.* 
FROM User 
  INNER JOIN user_workspace 
    ON  user_workspace.user_id = User._id 
  LEFT OUTER JOIN user_task 
    ON  user_task.user_id = User._id
    AND user_task.task_id = ?                <-- moved here
WHERE user_workspace.workspace_id = ?
  AND user_task.user_id IS NULL
ORDER BY User.name ASC ;

You could also use NOT EXISTS. I find the code more readable:

SELECT User.* 
FROM User 
  INNER JOIN user_workspace 
    ON  user_workspace.user_id = User._id 
WHERE user_workspace.workspace_id = ?
  AND NOT EXISTS
      ( SELECT *
        FROM user_task 
        WHERE user_task.user_id = User._id
          AND user_task.task_id = ? 
      )
ORDER BY User.name ASC ;

Best Answer

Related Solutions

Join for between multiple tables without common fields

Finding records not present across 3 tables – Sqlite3

Related Question