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
 

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

Open in new window

asiminatorAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Ok, then this should do it:
SELECT t1.thedatetime
, t1.chan1
, t1.chan2
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'

Open in new window

0
 
HoggZillaCommented:
Please post an example of your expected results based on the data you provided here. Thanks.
0
 
Kevin CrossChief Technology OfficerCommented:
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'

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Kevin CrossChief Technology OfficerCommented:
+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.
0
 
asiminatorAuthor Commented:
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
0
 
asiminatorAuthor Commented:
thanks... quick and easy
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.