Create a query that fetches rows plus their adjacent rows

Question

I am having problems with a specific query - respectively creating the query in the first place.
The columns can be reduced to id, seconds and status.

=============================
|  id  | seconds |  status  |
-----------------------------
|   0  |     0   |     0    |
|   1  |    12   |     1    |
|   2  |    25   |     0    |
|   3  |    37   |     1    |
|   4  |    42   |     0    |
=============================

What I'd like to have: All entries with status = 1 PLUS all entries that are less than 10 seconds away from those entries. Basically, I want to fetch all possible pairs (or triplets, etc.) of rows to check manually (later automatically) whether they need to be paired (there is a column parent_id for this purpose, but we don't need that for the query). I could do this in code (first select all status=1, then loop), but I wonder whether it is possible to do this purely in the database.

Thus, my desired output would be the following:

=============================
|  id  | seconds |  status  |
-----------------------------
|   1  |    12   |     1    | <- status = 1
|   3  |    37   |     1    | <- status = 1
|   4  |    42   |     0    | <- only 5 seconds after status = 1
=============================

My current best guess is this:

SELECT * FROM entries e0 
WHERE 
  e0.status = 1 OR 
  e0.status = 0 AND
  0 < (SELECT count(*) 
       FROM entries e1 
       WHERE e1.status = 1 AND abs(e1.seconds - e0.seconds) < 10)

But this fetches the whole table, and I don't really know why - and it takes a long time to do so (there is an index on the column seconds, the table has 9000 entries).

Is there a way to do this (maybe even effiently)?

Answer 1

Here's one option with union all and exists:

select * from entries where status = 1
union all
select * from entries e where status = 0 and 
  exists (select 1
          from entries e2 
          where e2.status = 1 and
                abs(e.seconds - e2.seconds) < 10
          )

• SQL Fiddle Demo

---

Alternatively you could use an outer join with distinct instead of exists:

select distinct e.*
from entries e
  left join entries e2 on e2.status = 1 
where e.status = 1 or abs(e.seconds - e2.seconds) < 10

• More Fiddle