Select rows where related rows only have unmatched years

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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>

Answer 4

Yet, there's another method to use;

to outer(left) join the tables by respective year columns, and then to filter by null valued year columns of the second one such as

SELECT p.* 
  FROM projects p
  LEFT JOIN project_finances pf
    ON pf.year_funding = p.year_construction
 WHERE pf.year_funding IS NULL

Demo