In an SQLite DB, I have teh following tables
*reports* id: integer name: string *sections* id: integer warning_table: string warning_kind: string report_id: integer (foreign key) *old_warnings* id:integer kind: string creation_date: date *new_warnings* id:integer kind: string creation_date: date
sections#warning_table can be either
new_warnings#kins can be one of the following values:
Now, given a date, I need to get the
reports records for which there is at least a record in either
new_warnings created that date of the kind as indicated in the
Here an example to clarify
REPORTS |id|name | |1 | first | |2 | second | SECTIONS |id|warning_table|warning_kind| report_id| |1 |old_warnings |small | 1 | |2 |old_warnings |medium | 2 | |3 |new_warning |small | 2 | OLD_WARNINGS |id|kind | creation_date| |1 |small | 2020-02-01 | |2 |medium| 2020-02-02 | NEW_WARNINGS |id|kind | creation_date| |1 |small | 2020-02-02 | |2 |medium| 2020-02-04 |
So when the date is
2020-02-01 I need to get only the
first report since there are neither medium old warnings or small new warnings created that day. And when the date is
2020-02-02 I should get back both reports since there is an old warning medium and a new warning small created that day.
Any ideas on how I can get what I need?
I have tried with
join but I was lost. So I tried with
union but also in that case I didn't go too far.