asiminator
asked on
simple sql query
Here is another one.
Say i have three tables as detailed below.
Table1 has a timestamp and then two values.
Table2 has a foreign key and times of periods that im interested in, in table1.
Table3 has an id and a name for these periods.
So what im looking for a is a query that will find me the using a name in table3 to find times in table2 to find data in table1.
So, what i would like is a quey that looked something like.
Select thedatetime, chan1
from table1 where thedatetime between (select starttime from table2 where id = (select id from table3 where name = 'period1') and (select endtime from table2 where id = (select id from table3 where name = 'period1') )
I know this query doesnt work... but i need one that does. Please ask any questions if this is not clear
Say i have three tables as detailed below.
Table1 has a timestamp and then two values.
Table2 has a foreign key and times of periods that im interested in, in table1.
Table3 has an id and a name for these periods.
So what im looking for a is a query that will find me the using a name in table3 to find times in table2 to find data in table1.
So, what i would like is a quey that looked something like.
Select thedatetime, chan1
from table1 where thedatetime between (select starttime from table2 where id = (select id from table3 where name = 'period1') and (select endtime from table2 where id = (select id from table3 where name = 'period1') )
I know this query doesnt work... but i need one that does. Please ask any questions if this is not clear
Table1
thedatetime, chan1, chan2
2008-10-20 14:15:16.1, 129, 33
2008-10-20 14:15:16.2, 132, 32
2008-10-20 14:15:16.3, 142, 13
2008-10-20 14:15:16.4, 152, 33
2008-10-20 14:15:16.5, 122, 43
2008-10-20 14:15:16.6, 122, 63
2008-10-20 14:15:16.7, 112, 37
2008-10-20 14:15:16.8, 112, 53
2008-10-20 14:15:16.9, 152, 34
2008-10-20 14:15:17.0, 121, 73
2008-10-20 14:15:17.1, 129, 33
2008-10-20 14:15:17.2, 132, 32
2008-10-20 14:15:17.3, 142, 13
2008-10-20 14:15:17.4, 152, 33
2008-10-20 14:15:17.5, 122, 43
2008-10-20 14:15:17.6, 122, 63
2008-10-20 14:15:17.7, 112, 37
2008-10-20 14:15:17.8, 112, 53
Table2
periodid_fk, starttime, endtime
1 2008-10-20 14:15:16.3, 2008-10-20 14:15:16.8
2 2008-10-21 10:55:16.3, 2008-10-21 11:11:12.0
3 2008-10-22 12:11:46.4, 2008-10-22 14:15:16.8
1 2008-10-20 14:15:17.2, 2008-10-20 14:15:17.5
Table3
id, name
1 period1
2 period2
3 period3
Steve Hogg
Please post an example of your expected results based on the data you provided here. Thanks.
Try something like this:
SELECT *
FROM Table3 t3
INNER JOIN Table2 t2 ON t2.periodid_fk = t3.id
INNER JOIN Table1 t1 ON t1.thedatetime BETWEEN t2.starttime AND t2.endtime
WHERE t3.name = 'period1'
+If rows in table1 will match to multiple date ranges from table2 just note you will get multiple of values in table1 matching to table2 meaning more rows than originally matched between table3 and table2.
ASKER
so my sample output would be if I wanted period1
thedatetime, chan1, chan2
2008-10-20 14:15:16.3, 142, 13
2008-10-20 14:15:16.4, 152, 33
2008-10-20 14:15:16.5, 122, 43
2008-10-20 14:15:16.6, 122, 63
2008-10-20 14:15:16.7, 112, 37
2008-10-20 14:15:16.8, 112, 53
2008-10-20 14:15:17.2, 132, 32
2008-10-20 14:15:17.3, 142, 13
2008-10-20 14:15:17.4, 152, 33
2008-10-20 14:15:17.5, 122, 43
thedatetime, chan1, chan2
2008-10-20 14:15:16.3, 142, 13
2008-10-20 14:15:16.4, 152, 33
2008-10-20 14:15:16.5, 122, 43
2008-10-20 14:15:16.6, 122, 63
2008-10-20 14:15:16.7, 112, 37
2008-10-20 14:15:16.8, 112, 53
2008-10-20 14:15:17.2, 132, 32
2008-10-20 14:15:17.3, 142, 13
2008-10-20 14:15:17.4, 152, 33
2008-10-20 14:15:17.5, 122, 43
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks... quick and easy