Hello,

i have 3 tables:

table1: stable
s_id | s_title
1 | stable1
2 | stable2

tabl2: animal
a_id | a_name
1 | Linda
2 | Berta
3 | Siggi

table3: removals
re_id | re_date | re_a_id | re_s_id
1 | 2017-01-10 | 1 | 1
2 | 2017-01-11 | 1 | 2
3 | 2017-01-11 | 2 | 1
4 | 2017-01-12 | 1 | 1
5 | 2017-01-13 | 1 | 2
6 | 2017-01-14 | 3 | 1


now i will check, how many animals were in Stable 1 on 2017-01-12 (answer: 2)



returns 3, cause I don't know how to realize it with the date

I hope you can help me :-)

Traxxas

It's not clear what the removal table represent. What is the re_date? Is it the date the animal was removed from the stable? But then, how do you know when it was added to the stable? Try explaining how this table is supposed to work first.

Hello Vlad,

thanks for you fast response.

the removal table looks like this:
table3: removals
re_id | re_date | re_a_id | re_s_id
1 | 2017-01-10 | 1 | 1
2 | 2017-01-11 | 1 | 2
3 | 2017-01-11 | 2 | 1
4 | 2017-01-12 | 1 | 1
5 | 2017-01-13 | 1 | 2
6 | 2017-01-14 | 3 | 1

explanation:
Linda join stable 1 on 2017-01-10 *
Linda join stable 2 on 2017-01-11 *
Berta join stable 1 on 2017-01-11
Linda join stable 1 on 2017-01-12
Berta join stable 2 on 2017-01-13
Siggi join stable 1 on 2017-01-14


* On this two lines you can see that Linda was in stable 1 from 2017-01-10 till 2017-01-11

I look for the two bold Lines.
Berta joined on 2017-01-11 and Linda on 2017-01-12

So, these 3 records



Means that Linda has first joined Stable1, then Stable2, and then it joined Stable1 again, right?

Yes

I'll answer with a blog post. Check out the solution on my blog.

Thanks a lot!! worked for me :-)