hetean hetean - 1 month ago 6
SQL Question

SQL use selected values in where statement

I use 2 SQL commands. First is to get some reference values, and the second is to filter with it. The results of the first command are used as :FINISH_TIME and :START_TIME in the second.

select FINISHED, STARTED from table
where id = :ID

select * from table
where (FINISHED <= :FINISH_TIME
and
FINISHED >= :START_TIME)
or
(STARTED <= :FINISH_TIME
and
STARTED >= :START_TIME)
or
(STARTED <= :START_TIME
and
FINISHED >= :FINISH_TIME
)


I would like to combine these two commands so I dont have to manually copy the reference values to the second command.

I found a solution like this:

FINISHED <= (select FINISHED from table where id = :ID)


but this is redundant. Is there a better way to do this, like use the searched value as a variable?

thanks for the help!

Answer

You can probably get the records with an overlapping time range with just one select.

select t.*
from yourtable t
inner join (
  select id, finished, started 
  from yourtable 
  where id = :id
  ) s
on (    t.finished >= s.started
    and t.started <= s.finished
    and t.started <= t.finished 
    --and t.id <> s.id
    );
Comments