Pharaoh Pharaoh - 5 months ago 11
SQL Question

Create a query that fetches rows plus their adjacent rows

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

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
          )

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