Re: Help with mysql query, multiple list

2009-05-10 Thread Scott Haneda 
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

2009-05-09 Thread Simon J Mudd 
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

2009-05-08 Thread Abhishek Pratap 
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

2009-05-08 Thread Abhishek Pratap 
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

2009-05-08 Thread Jim Lyons 
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

2009-05-08 Thread Abhishek Pratap 
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

2009-05-08 Thread Kyong Kim 

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

2009-05-08 Thread Jim Lyons 
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