sqloracle-databaseoracle18c

Select rows where related rows only have unmatched years


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 related rows.

The result would look like this:

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

How can I do that using Oracle 18c SQL?

Edit: My priority is conciseness; I'm not too worried about performance.


Solution

  • 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

    db<>fiddle here