MySQL: How to sum on joined queries (subqueries) with multiple conditions?

I have two tables:

Table Y: indicates over what time period money is valid

+-----------+---------+------+
| starttime | endtime | name |
+-----------+---------+------+
| 1         | 2       | Mark |
| 3.4       | 3.6     | Fred |
| 2         | 2.2     | Fred |
+-----------+---------+------+

Table Z

+-----------+---------+------+-------+
| starttime | endtime | name | money |
+-----------+---------+------+-------+
| 1.5       | 1.7     | Mark | 0.1   |
| 1.4       | 3.5     | Fred | 0.2   |
| 0.9       | 1.0     | Fred | 0.3   |
| 1.2       | 2.5     | Fred | 0.5   |
+-----------+---------+------+-------+

What I want:

+------+---------------+
| name | sum_validmoney|
+------+---------------+
| Mark | 0.1           |
| Fred | 0.7           | **=> this is 0.2+0.5 (because those rows overlapped in start/endtime segments)**
+------+---------------+

I want the to sum up rows in Table Z based on time segments constraints (bounded) indicated in Table Y. That is, the rows that are to be summed in Table Z must have time segments within those found in Table Y (there are also additional filters on Table Z for other queries; but I don’t think that’s relevant here)

I’m very new to MySQL. I’ve tried subqueries, but think I’m missing something key; I’m not sure if this can be done without some complicated loop structure which mysql doesn’t seem well-equipped for.

Answer

It looks like you want to filter table z on rows whose range overlap ranges at least one range in table y.

If so, one approach is:

select name, sum(money) sum_valid_money
from z
where exists (
    select 1
    from y 
    where y.name = x.name and y.starttime <= x.endtime and y.endtime >= x.starttime
)
group by name

Advertisement

Answer