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

select * from File where insertion_date <= 15 days ago.

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

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:

select * 
from files a 
where insertion_date <= sysdate -15
and not exists 
    (
    select 1 
    from files b 
    where a.article_id = b.article_id
    and b.insertion_date > a.insertion_date
    )