CODEWITHSUNDEEP
sqloracle-database
Vinnicius Santos
Vinnicius Santos

Reputation: 55

Select items only if they all meet the specific condition

I have two tables on a Oracle SQL DB.

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.

Upvotes: 0

Views: 39

Answers (1)

tinazmu
tinazmu

Reputation: 5139

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
    )

Upvotes: 1

Related Questions