[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

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

0
asiminator
Asked:
asiminator
  • 3
  • 2
1 Solution
 
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
 
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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
 
asiminatorAuthor Commented:
thanks... quick and easy
0

Featured Post

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.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now