Home » SQL & PL/SQL » SQL & PL/SQL » outer join and exsists
outer join and exsists [message #436491] Wed, 23 December 2009 17:58 Go to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
I have a table program and amendment

table, amendment has an FK to program

I need a query to find all programs which either
do not have amendments
or
have amendments with a status of complete

here I can do this
        select ap.sys_audit_prog_id,  from  program ap, amendment apg
        where
        ap.SYS_PROG_ID=apg.SYS_PROG_ID(+)
        and apg.STATUS='COMPLETED'


this query satisfies one filter ie amendment (status) but not the second filter (no amendment) ,how can I include the second filter in the same query ?,
ofcourse I can do it with union , but I am trying to avoid it , please suggest me if it is possibe
Re: outer join and exsists [message #436493 is a reply to message #436491] Wed, 23 December 2009 18:10 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: outer join and exsists [message #436496 is a reply to message #436493] Wed, 23 December 2009 18:22 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
here is the ddl statements u asked

 CREATE TABLE EA_AUDIT_PROGRAM
(
  SYS_AUDIT_PROG_ID    NUMBER                   NOT NULL,
  SYS_AUDIT_ID         NUMBER                   NOT NULL,
  SYS_PROG_ID          NUMBER                   NOT NULL,
  OBJ_VERSION          NUMBER,
  GO_USER_ID           NUMBER,
  OCD_DUE_DATE         DATE,
  ASSIGNMENT_MEMO      BLOB,
  MEMO_CREATED         CHAR(1 BYTE),
  ACD_SENT_TO_DFI      DATE,
  ADL_SENT_TO_GRANTEE  DATE,
  ACD_SENT_TO_OIG      DATE,
  NA_SENT_TO_PSC       DATE,
  PD_USER_ID           NUMBER,
  IS_LIVE              CHAR(1 BYTE)             NOT NULL
)

ALTER TABLE EA_AUDIT_PROGRAM ADD (
  CONSTRAINT EA_AUDIT_PROGRAM_PK
 PRIMARY KEY
 (SYS_AUDIT_PROG_ID)
 
 
 




 INSERT INTO EA_AUDIT_PROGRAM ( SYS_AUDIT_PROG_ID, SYS_AUDIT_ID, SYS_PROG_ID, OBJ_VERSION,
GO_USER_ID, OCD_DUE_DATE, MEMO_CREATED, ACD_SENT_TO_DFI, ADL_SENT_TO_GRANTEE, ACD_SENT_TO_OIG,
NA_SENT_TO_PSC, PD_USER_ID, IS_LIVE ) VALUES ( 
983, 43, 814, 0, 4779, NULL, 'Y', NULL, NULL, NULL, NULL, 4769, 'Y'); 
COMMIT;





CREATE TABLE EA_AUDIT_PROG_AMENDMENT
(
  SYS_AUDIT_PROG_AMENDMENT_ID  NUMBER           NOT NULL,
  SYS_AUDIT_PROG_ID            NUMBER           NOT NULL,
  AMENDMENT_NO                 NUMBER           NOT NULL,
  CREATED_ON                   DATE             NOT NULL,
  CREATED_BY                   NUMBER           NOT NULL,
  STATUS                       VARCHAR2(20 BYTE) NOT NULL,
  OBJ_VERSION                  NUMBER
)

ALTER TABLE EA_AUDIT_PROG_AMENDMENT ADD (
  CONSTRAINT EA_AUDIT_PROG_AMENDMENT_PK
 PRIMARY KEY
 (SYS_AUDIT_PROG_AMENDMENT_ID)


ALTER TABLE EA_AUDIT_PROG_AMENDMENT ADD (
  CONSTRAINT EA_AUDIT_PROG_AMENDMENT_R02 
 FOREIGN KEY (SYS_AUDIT_PROG_ID) 
 REFERENCES EA_AUDIT_PROGRAM (SYS_AUDIT_PROG_ID))
 
 
 INSERT INTO EA_AUDIT_PROG_AMENDMENT ( SYS_AUDIT_PROG_AMENDMENT_ID, SYS_AUDIT_PROG_ID, AMENDMENT_NO,
CREATED_ON, CREATED_BY, STATUS, OBJ_VERSION ) VALUES ( 
21, 983, 1,  TO_Date( '12/16/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 4779, 'PENDING'
, 0); 
COMMIT;
 
 
 
 


           select  ap.sys_audit_prog_id from  ea_audit_program ap, ea_audit_prog_amendment apg
          where
          ap.SYS_AUDIT_PROG_ID=apg.SYS_AUDIT_PROG_ID
           and apg.STATUS='COMPLETED'
           union
           select  sys_audit_prog_id from  ea_audit_program ap
           where ap.SYS_AUDIT_PROG_ID  not in (select sys_audit_prog_id     from ea_audit_prog_amendment)



 


I need suggestion on how to avoid that union and make it one query

[Updated on: Wed, 23 December 2009 18:25]

Report message to a moderator

Re: outer join and exsists [message #436497 is a reply to message #436496] Wed, 23 December 2009 19:01 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
What about NOT IN?

regards,
Delna
Re: outer join and exsists [message #436498 is a reply to message #436496] Wed, 23 December 2009 19:07 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
SQL> CREATE TABLE EA_AUDIT_PROGRAM
  2  (
  3    SYS_AUDIT_PROG_ID    NUMBER		     NOT NULL,
  4    SYS_AUDIT_ID	    NUMBER		     NOT NULL,
  5    SYS_PROG_ID	    NUMBER		     NOT NULL,
  6    OBJ_VERSION	    NUMBER,
  7    GO_USER_ID	    NUMBER,
  8    OCD_DUE_DATE	    DATE,
  9    ASSIGNMENT_MEMO	    BLOB,
 10    MEMO_CREATED	    CHAR(1 BYTE),
 11    ACD_SENT_TO_DFI	    DATE,
 12    ADL_SENT_TO_GRANTEE  DATE,
 13    ACD_SENT_TO_OIG	    DATE,
 14    NA_SENT_TO_PSC	    DATE,
 15    PD_USER_ID	    NUMBER,
 16    IS_LIVE		    CHAR(1 BYTE)	     NOT NULL
 17  )
 18  /

Table created.

SQL> ALTER TABLE EA_AUDIT_PROGRAM ADD
  2    CONSTRAINT EA_AUDIT_PROGRAM_PK
  3   PRIMARY KEY
  4   (SYS_AUDIT_PROG_ID)
  5  /

Table altered.

SQL>  INSERT INTO EA_AUDIT_PROGRAM ( SYS_AUDIT_PROG_ID, SYS_AUDIT_ID, SYS_PROG_ID, OBJ_VERSION,
  2  GO_USER_ID, OCD_DUE_DATE, MEMO_CREATED, ACD_SENT_TO_DFI, ADL_SENT_TO_GRANTEE, ACD_SENT_TO_OIG,
  3  NA_SENT_TO_PSC, PD_USER_ID, IS_LIVE ) VALUES (
  4  983, 43, 814, 0, 4779, NULL, 'Y', NULL, NULL, NULL, NULL, 4769, 'Y');

1 row created.

SQL>  INSERT INTO EA_AUDIT_PROGRAM ( SYS_AUDIT_PROG_ID, SYS_AUDIT_ID, SYS_PROG_ID, OBJ_VERSION,
  2  GO_USER_ID, OCD_DUE_DATE, MEMO_CREATED, ACD_SENT_TO_DFI, ADL_SENT_TO_GRANTEE, ACD_SENT_TO_OIG,
  3  NA_SENT_TO_PSC, PD_USER_ID, IS_LIVE ) VALUES (
  4  777, 43, 814, 0, 4779, NULL, 'Y', NULL, NULL, NULL, NULL, 4769, 'Y');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> 
SQL> CREATE TABLE EA_AUDIT_PROG_AMENDMENT
  2  (
  3    SYS_AUDIT_PROG_AMENDMENT_ID  NUMBER	     NOT NULL,
  4    SYS_AUDIT_PROG_ID	    NUMBER	     NOT NULL,
  5    AMENDMENT_NO		    NUMBER	     NOT NULL,
  6    CREATED_ON		    DATE	     NOT NULL,
  7    CREATED_BY		    NUMBER	     NOT NULL,
  8    STATUS			    VARCHAR2(20 BYTE) NOT NULL,
  9    OBJ_VERSION		    NUMBER
 10  )
 11  /

Table created.

SQL> ALTER TABLE EA_AUDIT_PROG_AMENDMENT ADD
  2    CONSTRAINT EA_AUDIT_PROG_AMENDMENT_PK
  3   PRIMARY KEY
  4   (SYS_AUDIT_PROG_AMENDMENT_ID)
  5  /

Table altered.

SQL> ALTER TABLE EA_AUDIT_PROG_AMENDMENT ADD
  2    CONSTRAINT EA_AUDIT_PROG_AMENDMENT_R02
  3   FOREIGN KEY (SYS_AUDIT_PROG_ID)
  4   REFERENCES EA_AUDIT_PROGRAM (SYS_AUDIT_PROG_ID)
  5  /

Table altered.

SQL>  INSERT INTO EA_AUDIT_PROG_AMENDMENT ( SYS_AUDIT_PROG_AMENDMENT_ID, SYS_AUDIT_PROG_ID, AMENDMENT_NO,
  2  CREATED_ON, CREATED_BY, STATUS, OBJ_VERSION ) VALUES (
  3  21, 983, 1,  TO_Date( '12/16/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 4779, 'PENDING'
  4  , 0);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> select ap.sys_audit_prog_id
  2  from  EA_AUDIT_PROGRAM ap
  3  where ap.SYS_PROG_ID IN (select apg.SYS_AUDIT_PROG_ID
  4  			      from EA_AUDIT_PROG_AMENDMENT apg
  5  			      WHERE apg.STATUS='COMPLETED')
  6  or (select count(0)
  7  	 from EA_AUDIT_PROG_AMENDMENT apg
  8  	 WHERE ap.SYS_PROG_ID = apg.SYS_AUDIT_PROG_ID ) = 0
  9  /

SYS_AUDIT_PROG_ID
-----------------
	      983
	      777

SQL> drop TABLE EA_AUDIT_PROG_AMENDMENT;

Table dropped.

SQL> DROP TABLE EA_AUDIT_PROGRAM;

Table dropped.
Re: outer join and exsists [message #436506 is a reply to message #436498] Wed, 23 December 2009 20:46 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
I am looking for a single query not two queries ,union , or
its two queries. Is there any other way ? may using not exsists ? or something else
Re: outer join and exsists [message #436507 is a reply to message #436506] Wed, 23 December 2009 20:51 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
I am looking for a single query not two queries ,union , or


What is the reason behind it?

regards,
Delna
Re: outer join and exsists [message #436508 is a reply to message #436507] Wed, 23 December 2009 20:57 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
well I am using hibernate and want to use criteira query which is object oriented , which does not support union
Re: outer join and exsists [message #436509 is a reply to message #436508] Wed, 23 December 2009 21:04 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
>well I am using hibernate
This is not our problem; just yours.

The two "requirements" are mutually exclusive.
Either a row exists in child table with correct status OR no row exists in child table!

Either change the requirements or use OR.
Accept basic logic!
Re: outer join and exsists [message #436510 is a reply to message #436508] Wed, 23 December 2009 21:12 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
FULL OUTER JOIN
WHERE amendment.col IS NULL
  OR STATUS = <val>


regards,
Delna
Previous Topic: SQL Query Help
Next Topic: multibyte characters to a csv file
Goto Forum:
  


Current Time: Sat Oct 01 04:08:24 CDT 2016

Total time taken to generate the page: 0.08949 seconds