hetean hetean - 1 year ago 70
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

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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download