SQL get only items with all values in column

Question

I have sql query like this:

SELECT m.id, 
e.id AS extraction_id, 
row_number() OVER (PARTITION BY m.id ORDER BY e.start_time DESC) as 
   start_date_rank,
e.end_time AS e_end_time, 
e.status AS e_status
FROM monitors m 
LEFT JOIN extractions e 
ON m.id = e.monitor_id 
WHERE m.is_deleted is FALSE

I am getting these rows:

My problem is, that I want only these ids, where in all e_end_time columns are timestams. For this example, it is only id 2. Can someone please help me, how to do it ?

Id 1 has timestamp, but only in first row. The second and third rows have null in e_end_time column, that is why it should not be returned.

Answer 1

Add a NOT IN sub query to your WHERE clause

WHERE m.is_deleted is FALSE
  AND m.id NOT IN (SELECT DISTINCT monitor_id 
                   FROM extractions
                   WHERE end_time IS NULL)