Find all Boxes containing files where all the files of the box have expiration date inferior to a DATE

Question

Title was complicated, here the request: I want to select boxes (box). All files (file) from a box need to have an expiration date inferior to '01/01/2022'; so does the box.

Here is a request not working, but giving the good JOIN :

select distinct b.code,b.EXPIRATION_DATE
from box b
join COMPONENT_A cpp on cpp.b_Id=b.Id
join COMPONENT_Z cpt on cpt.A_Id=cpp.Id
join file f on f.Z_Id =cpt.Id
where 
b.EXPIRATION_DATE < to_date('01.01.2022', 'dd.mm.yyyy')
 and f.EXPIRATION_DATE < to_date('01.01.2022', 'dd.mm.yyyy');

This request does not work because it still gives boxes where some files inside have EXPIRATION_DATE > to_date('01.01.2022', 'dd.mm.yyyy')

What I need Example :

Box_1(01/01/2001) : file1(01/01/1999); file2(01/01/2023) NOT SELECTED (because file 2)

Box_2(01/01/2024) : file1(01/01/1999); file2(01/01/2002) NOT SELECTED (box date)

Box_3(01/01/2001) : file1(01/01/1999); file2(01/01/2001) SELECTED (all < 01/01/2022)

I am sure there is a Group by somewhere, file table is a huge table so performance is important.

Answer 1

If expiration_date columns' datatype is DATE, then don't compare them to strings. Use date literal or TO_DATE function with appropriate format mask. Both options below:

where b.EXPIRATION_DATE < date '2022-01-01'
  and f.EXPIRATION_DATE < to_date('01.01.2022', 'dd.mm.yyyy')

---

As of the query itself, you didn't post sample data (CREATE TABLE and INSERT INTO statements; it is difficult for us to guess what might be written in those 4 tables) so I created my own, simplified test case.

I removed component% tables, but - what might be useful here - is the temp CTE which returns max expiration_date for each box - then you'd use it in the final query.

Something like this:

SQL> with box (code, expiration_date) as
  2    (select 'Box_1', date '2001-01-01' from dual union all
  3     select 'Box_3', date '2001-01-01' from dual
  4    ),
  5  tfile (code, cfile, expiration_date) as
  6    (select 'Box_1', 'file1', date '1999-01-01' from dual union all
  7     select 'Box_1', 'file2', date '2023-01-01' from dual union all
  8     --
  9     select 'Box_3', 'file1', date '1999-01-01' from dual union all
 10     select 'Box_3', 'file2', date '2001-01-01' from dual
 11    ),
 12  --
 13  temp as
 14    -- find MAX expiration_date for each box code
 15    (select code, max(expiration_date) expiration_date
 16     from tfile
 17     group by code
 18    )
 19  select b.code, b.expiration_date
 20  from box b join temp f on f.code = b.code
 21  where b.expiration_date < date '2022-01-01'
 22    and f.expiration_date < date '2022-01-01';

CODE  EXPIRATION
----- ----------
Box_3 01.01.2001

SQL>