Home>

I want to use SQL to get only the data that meets the conditions from the following two tables.
Specifically, I would like to get the date and time data from Table 1 and calculate the total value of the corresponding data from Table 2 based on that data.


Using the pair of ‘testdate’ and ‘id’ in Table1 as a key, get the date and time of ‘Start’ and ‘finish’,
I want to find the total of the ‘score’ of the ‘dodate’ in Table 2 included during that date and time.

Table1

testdate id Start finish
2010/5/10 A 2010/5/10 12:10 2010/5/10 15:10
2011/6/10 B 2011/6/10 13:00 2011/6/10 18:10
2011/6/10 B 2011/6/10 21:00 2011/6/10 23:00
2011/10/10 C 2011/10/10 9:10 2011/10/10 17:10
2011/10/15 C 2011/10/15 9:10 2011/10/15 17:10

Table2

dodate id score
2010/5/10 13:10 A 2
2010/5/10 14:10 A 3
2010/5/10 16:10 A 2
2011/6/10 14:00 B 3
2011/6/10 19:00 B 1
2011/6/10 22:00 B 2
2011/10/10 10:10 C Four
2011/10/10 11:10 C 2
2011/10/15 10:10 C Four

The result I want to output

testdate id gouke
2010/5/10 A Five
2011/6/10 B Five
2011/10/10 C 6
2011/10/15 C Four
select testdate, id, SUM (score) as gouke from table1, table2
・
・
・

I am sorry that the explanation is difficult to understand.
If anyone knows, please let me know.

  • Answer # 1

    With Access SQL, you'll get the desired result below.

    SELECT
        Table1.testdate,
        Table1.id,
        Sum (Table2.score) AS gouke
    FROM FROM
        Table1 INNER JOIN Table2 ON Table1.id = Table2.id
    WHERE
        Table2.dodae Between Table1.Start And Table1.finish
    GROUP BY
        Table1.testdate,
        Table1.id;