Select rows where related rows only have unmatched years-CodePudding

I have a PROJECTS table and a PROJECT_FINANCES table:

with 
projects (project_id, year_construction) as (
select 1, 2022 from dual union all
select 2, 2023 from dual union all
select 3, 2024 from dual union all
select 4, 2025 from dual
),
project_finances (project_id, year_funding) as (
select 1, 2022 from dual union all
select 2, 2022 from dual union all
select 2, 2023 from dual union all
select 3, 2025 from dual
)
select
    *
from
    projects

PROJECTS:

PROJECT_ID YEAR_CONSTRUCTION
---------- -----------------
         1              2022
         2              2023
         3              2024
         4              2025

PROJECT_FINANCES:

PROJECT_ID YEAR_FUNDING
---------- ------------
         1         2022

         2         2022
         2         2023

         3         2025

I want to select PROJECTS where the related rows in PROJECT_FINANCES only have years that don't match the parent project year.

For example, PROJECT 3; 2024 has a related project finance record PROJECT 3; 2025. So, there are related rows, but none of those rows' years match the year of the parent project. So I want to select that project.

I don't want to select PROJECT 4, since it doesn't have any matching rows.

The result would look like this:

PROJECT_ID YEAR_CONSTRUCTION
---------- -----------------
         3              2024

How can I do that using Oracle 18c SQL?

CodePudding user response：

Join the tables and use aggregation with the condition in the HAVING clause:

SELECT p.project_id, p.year_construction
FROM projects p INNER JOIN project_finances f
ON f.project_id = p.project_id
GROUP BY p.project_id, p.year_construction
HAVING COUNT(CASE WHEN p.year_construction = f.year_funding THEN 1 END) = 0;

See the demo.

CodePudding user response：

Thank you for CTEs!

How about

JOIN, which will fetch only matching projects, and then
MINUS set operator to find which matching projects don't have matching years?

SQL> with
  2  projects (project_id, year_construction) as (
  3  select 1, 2022 from dual union all
  4  select 2, 2023 from dual union all
  5  select 3, 2024 from dual union all
  6  select 4, 2025 from dual
  7  ),
  8  project_finances (project_id, year_funding) as (
  9  select 1, 2022 from dual union all
 10  select 2, 2022 from dual union all
 11  select 2, 2023 from dual union all
 12  select 3, 2025 from dual
 13  )
 14  select a.*
 15  from projects a join project_finances b on a.project_id = b.project_id
 16  minus
 17  select * from project_finances;

PROJECT_ID YEAR_CONSTRUCTION
---------- -----------------
         3              2024

SQL>

It would be even simpler if you'd want to get project_id = 4 because - technically speaking - it really doesn't have matching year (nor ID) in project_finances.

 14  select * from projects
 15  minus
 16  select * from project_finances;

PROJECT_ID YEAR_CONSTRUCTION
---------- -----------------
         3              2024
         4              2025

SQL>

CodePudding user response：

You can use EXISTS and, within the sub-query, aggregate and use a HAVING clause and conditional aggregation to check the corresponding year does not exist:

select *
from   projects p
WHERE  EXISTS(
  SELECT 1
  FROM   project_finances f
  GROUP BY f.project_id
  HAVING COUNT(CASE f.year_funding WHEN p.year_construction THEN 1 END) = 0
  AND    p.project_id = f.project_id
)

Which, for the sample data, outputs:

PROJECT_ID YEAR_CONSTRUCTION

3 2024

PROJECT_ID	YEAR_CONSTRUCTION
3	2024

db<>fiddle here