Home » SQL & PL/SQL » SQL & PL/SQL » how to use not exists clause in this scenario or do we have any other option (oracle 10g)
how to use not exists clause in this scenario or do we have any other option [message #445885] Thu, 04 March 2010 08:44 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member

i want to fetch those records in which it should not match either of the BACK_BRANCH AND FRD_BRANCH's with respect to the invoice table.

if you see there are two branches are there in invoice table 'LON' AND 'NYR' in which 'NYR'
is not matched with either of BACK_BRANCH and FRD_BRANCH columns in document table.i tried with below query could not get this record.





SELECT *
  FROM DOCUMENT HDR, REFERENCE REF
 WHERE HDR.DOCUMENTID = '1234'
   AND REF.DOCUMENTID = HDR.DOCUMENTID
   AND HDR.BACK_BRANCH IS NOT NULL
   AND NOT EXISTS (SELECT 'X'
          FROM INVOICE INV
         WHERE INV.REFERENCE_ID = REF.REFERENCE_ID
           AND HDR.BACK_BRANCH = INV.BRANCH
           AND HDR.FRD_BRANCH = INV.BRANCH);

CREATE TABLE DOCUMENT(DOCUMENTID VARCHAR2(50),BACK_BRANCH VARCHAR2(50),FRD_BRANCH VARCHAR2(50));
CREATE TABLE REFERENCE(REFERENCE_ID VARCHAR2(50),DOCUMENTID VARCHAR2(50));
CREATE TABLE INVOICE(INVOICE_ID VARCHAR2(50),REFERENCE_ID VARCHAR2(50),BRANCH VARCHAR2(50));

INSERT INTO DOCUMENT(DOCUMENTID,BACK_BRANCH,FRD_BRANCH) VALUES(1234,'LON','LON');

INSERT INTO REFERENCE(REFERENCE_ID, DOCUMENTID) VALUES('LON91926','1234');

INSERT INTO INVOICE(INVOICE_ID, REFERENCE_ID,BRANCH) VALUES('LON1234','LON91926','LON');
INSERT INTO INVOICE(INVOICE_ID, REFERENCE_ID,BRANCH) VALUES('LON3535','LON91926','NYR');


[Updated on: Thu, 04 March 2010 08:50]

Report message to a moderator

Re: how to use not exists clause in this scenario or do we have any other option [message #445887 is a reply to message #445885] Thu, 04 March 2010 09:06 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
I don't follow why you think you want that record returned.
Re: how to use not exists clause in this scenario or do we have any other option [message #445890 is a reply to message #445887] Thu, 04 March 2010 09:12 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
there are 2 records in INVOICE table one is 'LON' branch and other one is 'NYR' branch.

'LON' matches with BACK_BRANCH and also FRD_BRANCH fields but 'NYR' doesn't match with either of these fields.
Re: how to use not exists clause in this scenario or do we have any other option [message #445891 is a reply to message #445885] Thu, 04 March 2010 09:19 Go to previous message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
So you want records that match both your branches?
Why are you using NOT EXISTS then?
Previous Topic: Truncate table error
Next Topic: Trigger Throw Error Msg
Goto Forum:
  


Current Time: Tue Dec 06 00:22:30 CST 2016

Total time taken to generate the page: 0.12060 seconds