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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve HoggITCommented:
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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
Kevin CrossChief 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
asiminatorAuthor Commented:
thanks... quick and easy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.