Selecting records based of other table records

Question

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 old_warnings or new_warnings. sections#warning_kind, old_warnings#kind, and new_warnings#kins can be one of the following values: small, medium, or large.

Now, given a date, I need to get the reports records for which there is at least a record in either old_warnings or new_warnings created that date of the kind as indicated in the sections.

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 count + union but also in that case I didn't go too far.

Answer 1

Putting data (here: "old"/"new") into the table name is a bad idea becaus it usually complicates queries. It should be stored in a column instead.

The easiest way to handle this is to create such a table will all warnings:

WITH all_warnings(warning_table, id, warning_kind, creation_date) AS (
  SELECT 'old_warnings', id, kind, creation_date FROM old_warnings
  UNION ALL
  SELECT 'new_warnings', id, kind, creation_date FROM new_warnings
)
SELECT *
FROM reports
WHERE id IN (SELECT report_id
             FROM sections
             JOIN all_warnings USING (warning_table, warning_kind)
             WHERE creation_date = ?
            );

If you need all warnings in other queries, then you should make all_warnings a view.