Re: Help with mysql query, multiple list
What about sub selects. As I see it you only care about the highest and lowest order of results in each list. Sorry, in am on a mobile so I can nit make a test case, and this will be pseudo SQL. Select * from table where start = (select foo) and ( select foo) ... Also look at the between and in keywords. Perhaps your list of timestamps is not in a database, can you put then in? Either way unless I am reading you wrong you only need the greatest and lowest time in your comparison, not a huge list. -- Scott Iphone says hello. On May 8, 2009, at 9:26 AM, Abhishek Pratap abhishek@gmail.com wrote: Hi All 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. Thanks, -Abhi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with mysql query, multiple list
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
Help with mysql query, multiple list
Hi All 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. Thanks, -Abhi
Re: Help with mysql query, multiple list
aah okie I think I was trying to get too clever. Guess that won't work ... Thanks, -Abhi On Fri, May 8, 2009 at 12:34 PM, Barney Boisvert bboisv...@gmail.comwrote: You'll have to iterate over your two lists of timestamps and build a set of ORed conditional pairs: sql = select ... from ... where 1 = 0 for (i = 0; i timestamps.length; i++) { sql += or start = + timestamps[i] + and end = + timestamps[i] } You'll want to use bind parameters in real life, of course. cheers, barneyb On Fri, May 8, 2009 at 9:26 AM, Abhishek Pratap abhishek@gmail.com wrote: Hi All 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. Thanks, -Abhi -- Barney Boisvert bboisv...@gmail.com http://www.barneyb.com/
Re: Help with mysql query, multiple list
why not something like below. Assume you have 3 pairs of start/end timestamps and you want to find everything within those 3 time periods: select * from table_name where start = start1 and end = end1 union select * from table_name where start = start2 and end = end2 union select * from table_name where start = start3 and end = end3 On Fri, May 8, 2009 at 11:26 AM, Abhishek Pratap abhishek@gmail.comwrote: Hi All 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. Thanks, -Abhi -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Help with mysql query, multiple list
Hi Jim Unfortunately I have thousands of such points. So explicit statement calling will be very expensive both computationally and in terms of writing.. Thanks, -Abhi On Fri, May 8, 2009 at 12:37 PM, Jim Lyons jlyons4...@gmail.com wrote: why not something like below. Assume you have 3 pairs of start/end timestamps and you want to find everything within those 3 time periods: select * from table_name where start = start1 and end = end1 union select * from table_name where start = start2 and end = end2 union select * from table_name where start = start3 and end = end3 On Fri, May 8, 2009 at 11:26 AM, Abhishek Pratap abhishek@gmail.comwrote: Hi All 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. Thanks, -Abhi -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Help with mysql query, multiple list
Abhi, I might not be understanding the problem but could you use the max and min timestamp values and use something like SELECT * FROM TABLE WHERE start BETWEEN max AND min AND end BETWEEN max AND min or SELECT * FROM TABLE WHERE START IN (1,2,3,4,5) AND END IN(1,2,3,4,5) I might be completely off-base here though as I don't think I fully comprehend what you're trying to do. Kyong At 09:36 AM 5/8/2009, Abhishek Pratap wrote: aah okie I think I was trying to get too clever. Guess that won't work ... Thanks, -Abhi On Fri, May 8, 2009 at 12:34 PM, Barney Boisvert bboisv...@gmail.comwrote: You'll have to iterate over your two lists of timestamps and build a set of ORed conditional pairs: sql = select ... from ... where 1 = 0 for (i = 0; i timestamps.length; i++) { sql += or start = + timestamps[i] + and end = + timestamps[i] } You'll want to use bind parameters in real life, of course. cheers, barneyb On Fri, May 8, 2009 at 9:26 AM, Abhishek Pratap abhishek@gmail.com wrote: Hi All 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. Thanks, -Abhi -- Barney Boisvert bboisv...@gmail.com http://www.barneyb.com/ Kyong Kim Instructional Multimedia/Web Programmer Foothill College 12345 El Monte Rd 3601 Los Altos Hills, CA 94022 650-949-7091 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with mysql query, multiple list
then either build the statement by way of a program like a perl script or select all records with a start time after the min start time of all in your list and an end time less than the max end time in your list then filter them further either in a program or a store procedure. On Fri, May 8, 2009 at 11:45 AM, Abhishek Pratap abhishek@gmail.comwrote: Hi Jim Unfortunately I have thousands of such points. So explicit statement calling will be very expensive both computationally and in terms of writing.. Thanks, -Abhi On Fri, May 8, 2009 at 12:37 PM, Jim Lyons jlyons4...@gmail.com wrote: why not something like below. Assume you have 3 pairs of start/end timestamps and you want to find everything within those 3 time periods: select * from table_name where start = start1 and end = end1 union select * from table_name where start = start2 and end = end2 union select * from table_name where start = start3 and end = end3 On Fri, May 8, 2009 at 11:26 AM, Abhishek Pratap abhishek@gmail.comwrote: Hi All 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. Thanks, -Abhi -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com