SQL (Oracle) -选择不存在记录的地方或第二个表中的另一列为真

Question

      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

我正在尝试编写一个sql语句，该语句查看类似于上面示例的表。我需要的结果是存在于应用程序表中的记录，该应用程序表具有应用程序类型的教育和状态类型的已批准，但在赋值表中没有记录。但是，如果赋值表中存在记录，则非活动列必须为1。

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

我也尝试了下面的sql，但之后我得到了约翰·史密斯、简·多伊和萨拉·简。

    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)

我希望能得到的唱片是约翰·史密斯和萨拉·简。目前，我得到了这两个记录和简无名氏，这是我不想在我的结果集。

Answer 1

我在想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
                 );

如果您想使用left join来表示这一点，那么逻辑是：

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;

这里是db<>fiddle。

Answer 2

不应在where条件下使用左联接表列(否则此工作将作为内部联接)。

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

您应该将这些移到related子句中，并让其中只包含空值的条件(而不是in)。

Answer 3

您可以尝试以下几种方法：

SELECT appl.id,
       appl.first_name,
       appl.last_name
FROM   applications appl
WHERE  appl.application_type = 'EDUCATION'
  AND  appl.status_type = 'APPROVED'
  AND  NOT EXISTS (SELECT *
                   FROM   assignments asgn
                   WHERE  appl.id = asgn.application_id
                     AND  asgn.inactive = 0)