0

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.

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.