我有一个父表和子表，其中父参考编号在子表中具有多个状态为“待定”、“成功”和“已拒绝”的条目。我需要一个查询，该查询将仅输出状态为“成功”和“已拒绝”的参考编号。即使子表中的参考编号具有一个待定状态，也不应列出该参考编号。

CREATE TABLE SI_DETAIL
  ( "S_NO" VARCHAR2(20 BYTE),
    "CREATED_DATE" DATE);
    
    
CREATE TABLE SI_TRANSDETAIL
  ( "S_NO"  VARCHAR2(20 BYTE),
    "SL_NO" VARCHAR2(20 BYTE),
    "EXE_DATE" DATE,
    "STATUS" VARCHAR2(20 BYTE));
    
    
Insert into SI_DETAIL (S_NO,CREATED_DATE) values ('1000',to_date('29-11-22','DD-MM-RR'));
Insert into SI_DETAIL (S_NO,CREATED_DATE) values ('1001',to_date('01-12-22','DD-MM-RR'));
Insert into SI_DETAIL (S_NO,CREATED_DATE) values ('1002',to_date('30-11-22','DD-MM-RR'));

    
    
Insert into SI_TRANSDETAIL (S_NO,SL_NO,EXE_DATE,STATUS) values ('1000','1',to_date('30-11-22','DD-MM-RR'),'REJECTED');
Insert into SI_TRANSDETAIL (S_NO,SL_NO,EXE_DATE,STATUS) values ('1000','2',to_date('01-12-22','DD-MM-RR'),'SUCCESS');
Insert into SI_TRANSDETAIL (S_NO,SL_NO,EXE_DATE,STATUS) values ('1000','3',to_date('02-12-22','DD-MM-RR'),'SUCCESS');
Insert into SI_TRANSDETAIL (S_NO,SL_NO,EXE_DATE,STATUS) values ('1001','1',to_date('02-12-22','DD-MM-RR'),'SUCCESS');
Insert into SI_TRANSDETAIL (S_NO,SL_NO,EXE_DATE,STATUS) values ('1001','2',to_date('03-12-22','DD-MM-RR'),'PENDING');
Insert into SI_TRANSDETAIL (S_NO,SL_NO,EXE_DATE,STATUS) values ('1001','3',to_date('04-12-22','DD-MM-RR'),'PENDING');
Insert into SI_TRANSDETAIL (S_NO,SL_NO,EXE_DATE,STATUS) values ('1001','4',to_date('05-12-22','DD-MM-RR'),'PENDING');
Insert into SI_TRANSDETAIL (S_NO,SL_NO,EXE_DATE,STATUS) values ('1002','1',to_date('04-12-22','DD-MM-RR'),'PENDING');

我现在已经尝试使用下面的查询并获得了所需的输出。输出应该只返回1000，因为它没有挂起的子记录。有更好的解决方案吗

SELECT S_NO FROM SI_TRANSDETAIL DTL
WHERE  EXISTS (SELECT S_NO FROM SI_DETAIL HD
               WHERE  DTL.S_NO = HD.S_NO  
                      AND CREATED_DATE < SYSDATE - 1
                      AND HD.S_NO NOT IN (SELECT S_NO 
                                          FROM   SI_TRANSDETAIL SUB 
                                          WHERE  STATUS = 'PENDING' 
                                                 AND SUB.S_NO = HD.S_NO));