abhishek....@gmail.com (Abhishek Pratap) writes: > I am kind of stuck with this query , cant expand my thinking. May this is a > limitation. Here it is > > I have a database with many cols two of which are start and end position for > an event. > > Now I have a list of event time stamps, I want to find all the info once the > current event time stamp is >= start time of event and <=end time of event. > > something like this > > select * from table_name where start <= ( LIST of time stamps) AND end >=( > list of time stamps). > > Clearly above query accepts only one item in the list. Is there a way to do > this for multiple items in the list ??? I can't think of anything at this > moment.
You said in a later post that you have thousands of events. If they are already in a table then use that, otherwise put the events into a temporary table and join the 2 tables together. Something like this simple example: mysql> select * from events; +---------------------+ | event_ts | +---------------------+ | 2009-05-09 10:29:00 | +---------------------+ 1 row in set (0.00 sec) mysql> select * from table_name; +----+---------------------+---------------------+ | id | start_ts | end_ts | +----+---------------------+---------------------+ | 1 | 2009-05-09 10:00:00 | 2009-05-09 11:00:00 | | 2 | 2009-05-10 10:00:00 | 2009-05-10 11:00:00 | +----+---------------------+---------------------+ 2 rows in set (0.00 sec) mysql> select t.* from table_name t, events WHERE event_ts >= start_ts and event_ts <= end_ts; +----+---------------------+---------------------+ | id | start_ts | end_ts | +----+---------------------+---------------------+ | 1 | 2009-05-09 10:00:00 | 2009-05-09 11:00:00 | +----+---------------------+---------------------+ 1 row in set (0.00 sec) Hope this helps. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
