Find all Boxes containing files where all the files of the box have expiration date inferior to a DA-CodePudding

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.

CodePudding user response：

To check that the maximum file date for each box code is less than 2022-01-01, you can use:

SELECT b.code,
       MAX(b.EXPIRATION_DATE) AS 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 < DATE '2022-01-01'
GROUP BY b.code
HAVING MAX(f.EXPIRATION_DATE) < DATE '2022-01-01';

or, to invert the condition and check that there are no files in a box that are greater than or equal to 2022-01-01, you can use:

SELECT code,
       expiration_date
FROM   box b
WHERE  EXPIRATION_DATE < DATE '2022-01-01'
AND    NOT EXISTS (
         SELECT 1
         FROM   COMPONENT_A cpp
                join COMPONENT_Z cpt on cpt.A_Id=cpp.Id
                join "FILE" f on f.Z_Id =cpt.Id
         WHERE  cpp.b_Id = b.Id
         AND    f.EXPIRATION_DATE >= DATE '2022-01-01'
       );

(Note: there is a slight difference between the two queries when a box has no files. The first query will not return a box with no files whereas the second query would.)

Which, for the sample data:

CREATE TABLE box (id, code, expiration_date) AS
SELECT 1, 'Box_1', DATE '2001-01-01' FROM DUAL UNION ALL
SELECT 2, 'Box_2', DATE '2024-01-01' FROM DUAL UNION ALL
SELECT 3, 'Box_3', DATE '2001-01-01' FROM DUAL;

CREATE TABLE component_a (id, b_id) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 2, 2 FROM DUAL UNION ALL
SELECT 3, 3 FROM DUAL;

CREATE TABLE component_z (id, a_id) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 2, 2 FROM DUAL UNION ALL
SELECT 3, 3 FROM DUAL;

CREATE TABLE "FILE" (id, z_id, expiration_date) AS
SELECT 1, 1, DATE '1999-01-01' FROM DUAL UNION ALL
SELECT 2, 1, DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 3, 2, DATE '1999-01-01' FROM DUAL UNION ALL
SELECT 4, 2, DATE '2002-01-01' FROM DUAL UNION ALL
SELECT 5, 3, DATE '1999-01-01' FROM DUAL UNION ALL
SELECT 6, 3, DATE '2001-01-01' FROM DUAL;

Both output:

CODE EXPIRATION_DATE

Box_3 01-JAN-01

CODE	EXPIRATION_DATE
Box_3	01-JAN-01

db<>fiddle here

CodePudding user response：

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>