I have 2 tables. One table listing results of a run and a 2nd table with timestamps.

table1 (not sure how to add another column to the table but table 1 has an end time also that will need to be filtered out):

table 2:

The goal is to filter out/ exlude rows from table 1 that fall in between start/end time from table 2.

The results should be :

I tried doing below:

select * from table 1 where start not in (select start from table 2)

but does not seem to work correctly. Any advice will be much appreciated!

CodePudding user response：

Using exists logic we can try:

SELECT t1.*
FROM table1 t1
WHERE NOT EXISTS (
    SELECT 1
    FROM table2 t2
    WHERE t1.start BETWEEN t2.start AND t2.end
);

For your updated requirement, we could select an exists boolean expression:

SELECT t1.*, NOT EXISTS (
    SELECT 1
    FROM table2 t2
    WHERE t1.start BETWEEN t2.start AND t2.end
) AS mw
FROM table1 t1;