SQL (Oracle) – Select where record does not exist or another column in the 2nd table is true

      Applications-Table  
    ---------------------
ID          FIRST_NAME          LAST_NAME          APPLICATION_TYPE          STATUS_TYPE
123         JOHN                SMITH              EDUCATION                 APPROVED
456         JANE                DOE                EDUCATION                 APPROVED
789         SARA                JANE               EDUCATION                 APPROVED
321         BOB                 KANE               GENERAL                   APPROVED
879         OLIVER              QUEEN              GENERAL                   APPROVED

     Assignments-Table
ID          APPLICATION_ID         INACTIVE
0001        456                    0
0002        789                    1

I am trying to write a sql statement that looks at tables similar to the example above. What I need for results is a record that exists in the applications table that has an application type of education and a status type of approved but does not have a record in the assignments table. However if a record exists in the assignments table then the Inactive column has to be 1. Below is the sql I’ve been working with

SELECT 
    application.ID,
    application.FIRST_NAME,
    application.LAST_NAME,
FROM applications application
LEFT JOIN assignments assignment ON application.ID = assignment.APPLICATION_ID
WHERE application.STATUS_TYPE = 'APPROVED' 
AND application.APPLICATION_TYPE = 'EDUCATION'
AND assignment.APPLICATION_ID IS NULL OR assignment.INACTIVE != 0

I also tried the below sql but then I get John Smith, Jane Doe, and Sara Jane.

    SELECT 
        application.ID,
        application.FIRST_NAME,
        application.LAST_NAME,
    FROM applications application
    LEFT JOIN assignments assignment ON application.ID = 
    assignment.APPLICATION_ID
WHERE application.STATUS_TYPE = 'APPROVED' 
AND application.APPLICATION_TYPE = 'EDUCATION'
AND (assignment.APPLICATION_ID IS NULL OR assignment.INACTIVE != 0)

The records I’d expect to get back would be John Smith and Sara Jane. Currently I am getting those 2 records and Jane Doe which I do not want in my results set.

Answer

I am thinking not exists:

select a.*
from applications a
where a.STATUS_TYPE = 'APPROVED' and
      a.APPLICATION_TYPE = 'EDUCATION' and
      not exists (select 1
                  from assignments am
                  where a.ID = am.APPLICATION_ID and am.inactive <> 1
                 );

If you want to express this using left join, the logic is:

select a.*
from applications a left join
     assignments am
     on a.ID = am.APPLICATION_ID and am.inactive <> 1
where a.STATUS_TYPE = 'APPROVED' and
      a.APPLICATION_TYPE = 'EDUCATION' and
      am.application_id is null;

Here is a db<>fiddle.