Skip to content
Advertisement

Select items only if they all meet the specific condition

I have two tables on a Oracle SQL DB.

  • Article (id [PK])

  • File (id [PK], article_id [FK], insertion_date [DATETIME]).

I want to select files from articles that are more than 15 days old without adding new files.

For example, if post ID 1 has a file added 10 days ago and another 20 days ago, they should not be returned in the select, but if article 2 has 4 files added more than 15 days ago, it should be returned.

I tried performing a

But post archives that had recent additions continued to be returned.

Advertisement

Answer

We look for all entries in files with insertion_date more than 15 days old, and for each article we check for the existence of a more recent row, we reject if it does:

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement