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