Home » SQL & PL/SQL » SQL & PL/SQL » how to write this query using correlation subquery or non exists clause (oracle10g)
how to write this query using correlation subquery or non exists clause [message #385793] Wed, 11 February 2009 01:13 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
-- Tables description.
--step-1- 4 employees present in EMP table.
--step-2- each employee having 1 country_no in EMP_DOCS table.3 employees having same country_no(i.e emp's 1,2,3)
--step-3- 1 emp document can have multiple items.In this case each employee having one each in the EMP_ITEMS table.
--step-4- 1 EMPLOYEE can have Multiple Documents so we have a relation between EMP_ITEMS and DOCUMENT_ITEMS.whatever items present in EMP_ITEMS those items will be inserted into DOCUMENT_ITEMS.so we have a item-item relation.
--so we stored EMP_ITEMS id in EMP_ITEMS_REF_ID_1 of DOCUMENT_ITEMS table.
-- step-5- DOCUMENT-INVOICE has 1-1 relation once invoice is created we stored invoice id in DOCUMENT table.

--This is the requirement.Let's say in this example 3 employees are using same country_no and 4th employee is using another country_no
--which is not used by other 3 employees.

--Condtion-1:
--if all of the employees have created INVOICE which is using same country_no of different country_no then the query should display all records.

--Condition-2:
--if any one of the employee not created INVOICE which is using same country_no of other employees then remaining employees also should not come in the query
-- even though invoice is created by other employees.

--Condition-3:
--if any one of the employee not created even DOCUMENT which is using same country_no of other employees then remaining employees also should not come in the query
-- even though invoice is created by other employees.


I hope I explain the conditions clearly.if you understand well by looking at the data i posted below may i know what is the final result should be displayed?


create table EMP
(
  ID       NUMBER not null,
  TYPE     VARCHAR2(1)
);
alter table EMP
  add constraint ID primary key (ID);
 
create table EMP_DOCS
(
  ID         NUMBER not null,
  EMP_ID     NUMBER,
  COUNTRY_NO VARCHAR2(15)
);
alter table EMP_DOCS
  add constraint PK_EMP_DT primary key (ID);
 
alter table EMP_DOCS
  add constraint FK_EMP_DT foreign key (EMP_ID)
  references EMP (ID);
 
create table EMP_ITEMS
(
  ID         NUMBER not null,
  EMP_DOC_ID     NUMBER
);
 
alter table EMP_ITEMS
  add constraint PK_EMP_ITEMS_DT primary key (ID);
 
alter table EMP_ITEMS
  add constraint FK_EMP_ITEMS_DT foreign key (EMP_DOC_ID)
  references EMP_DOCS (ID);
 
 
  create table DOCUMENT
(
  ID   NUMBER not null,
  DOCNO VARCHAR2(15),
  INVOICE_REF_1 NUMBER
);
alter table DOCUMENT
  add constraint DOC_PK_ID primary key (ID);
 
 
  create table DOCUMENT_ITEMS
(
  ID         NUMBER not null,
  DOC_ID     NUMBER,
  EMP_ITEMS_REF_ID_1    NUMBER
);
 
alter table DOCUMENT_ITEMS
  add constraint PK_DOCUMENT_ITEMS_DT primary key (ID);
 
alter table DOCUMENT_ITEMS
  add constraint FK_DOCUMENT_ITEMS_DT foreign key (DOC_ID)
  references DOCUMENT (ID);
 
create table INVOICE
(
  ID   NUMBER not null,
  INVOICE_NO VARCHAR2(15)
);
alter table INVOICE
  add constraint INVOICE_PK_ID primary key (ID);
 
 
  INSERT INTO EMP ( ID, TYPE ) VALUES ( 
1, 'A'); 
INSERT INTO EMP ( ID, TYPE ) VALUES ( 
2, 'A'); 
INSERT INTO EMP ( ID, TYPE ) VALUES ( 
3, 'A'); 
INSERT INTO EMP ( ID, TYPE ) VALUES ( 
4, 'A'); 
 
INSERT INTO EMP_DOCS ( ID, EMP_ID, COUNTRY_NO ) VALUES ( 
1, 1, 'INDIA'); 
INSERT INTO EMP_DOCS ( ID, EMP_ID, COUNTRY_NO ) VALUES ( 
2, 2, 'INDIA'); 
INSERT INTO EMP_DOCS ( ID, EMP_ID, COUNTRY_NO ) VALUES ( 
3, 3, 'INDIA'); 
INSERT INTO EMP_DOCS ( ID, EMP_ID, COUNTRY_NO ) VALUES ( 
4, 4, 'USA'); 
 
INSERT INTO EMP_ITEMS ( ID, EMP_DOC_ID ) VALUES ( 
1, 1); 
INSERT INTO EMP_ITEMS ( ID, EMP_DOC_ID ) VALUES ( 
2, 2); 
INSERT INTO EMP_ITEMS ( ID, EMP_DOC_ID ) VALUES ( 
3, 3); 
INSERT INTO EMP_ITEMS ( ID, EMP_DOC_ID ) VALUES ( 
4, 4); 
 
INSERT INTO DOCUMENT ( ID, DOCNO, INVOICE_REF_1 ) VALUES ( 
1, 'DOC1', 1); 
INSERT INTO DOCUMENT ( ID, DOCNO, INVOICE_REF_1 ) VALUES ( 
2, 'DOC1', 2); 
INSERT INTO DOCUMENT ( ID, DOCNO, INVOICE_REF_1 ) VALUES ( 
3, 'DOC3', 0); 
INSERT INTO DOCUMENT ( ID, DOCNO, INVOICE_REF_1 ) VALUES ( 
4, 'DOC4', 3); 
 
INSERT INTO DOCUMENT_ITEMS ( ID, DOC_ID, EMP_ITEMS_REF_ID_1 ) VALUES ( 
1, 1, 1); 
INSERT INTO DOCUMENT_ITEMS ( ID, DOC_ID, EMP_ITEMS_REF_ID_1 ) VALUES ( 
2, 2, 2); 
INSERT INTO DOCUMENT_ITEMS ( ID, DOC_ID, EMP_ITEMS_REF_ID_1 ) VALUES ( 
3, 2, 2); 
INSERT INTO DOCUMENT_ITEMS ( ID, DOC_ID, EMP_ITEMS_REF_ID_1 ) VALUES ( 
4, 3, 3); 
INSERT INTO DOCUMENT_ITEMS ( ID, DOC_ID, EMP_ITEMS_REF_ID_1 ) VALUES ( 
5, 4, 4); 
 
INSERT INTO INVOICE ( ID, INVOICE_NO ) VALUES ( 
1, 'INV1'); 
INSERT INTO INVOICE ( ID, INVOICE_NO ) VALUES ( 
2, 'INV2'); 
INSERT INTO INVOICE ( ID, INVOICE_NO ) VALUES ( 
3, 'INV3'); 
commit;
 
 
-- I have written below query to satisfy above conditions but still required results are not coming..
SELECT *
  FROM EMP E
 WHERE E.TYPE = 'A'
   AND NOT EXISTS (SELECT *
          FROM EMP_DOCS       EDOC1,
               EMP_DOCS       EDOC2,
               EMP            E1,
               EMP_ITEMS      EMPI,
               DOCUMENT_ITEMS DOCITM,
               DOCUMENT       DOC,
               INVOICE        INV
         WHERE EDOC1.EMP_ID = E.ID
           AND EDOC2.EMP_ID = E1.ID
           AND EDOC1.ID = EMPI.EMP_DOC_ID
           AND EDOC1.COUNTRY_NO = EDOC2.COUNTRY_NO
           AND EMPI.ID = DOCITM.EMP_ITEMS_REF_ID_1
           AND DOCITM.DOC_ID = DOC.ID
           AND INV.ID = DOC.INVOICE_REF_1);


Re: how to write this query using correlation subquery or non exists clause [message #385842 is a reply to message #385793] Wed, 11 February 2009 06:09 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@chaituu,
To Begin with, thanks for the test case. I got some free time and I was going through the conditions you have mentioned.

The following condition totally blew me off.
chaituu wrote on Wed, 11 February 2009 12:43

--Condtion-1:
--if all of the employees have created INVOICE which is using same country_no of different country_no then the query should display all records.


Can you please give some examples(based on the sample data you posted) for your conditions? Post your desired results(final output) as well.

Regards,
Jo
Re: how to write this query using correlation subquery or non exists clause [message #386052 is a reply to message #385793] Thu, 12 February 2009 06:20 Go to previous message
chaituu
Messages: 115
Registered: June 2008
Senior Member
--Condtion-1:
--if all of the employees have created INVOICE which is using same country_no of different country_no then the query should display all records.

actullay above is typing mistake.it should be

if all of the employees have created upto INVOICE which is using same country_no or different country_no then the query should display all records.


Can you please give some examples(based on the sample data you posted) for your conditions? Post your desired results(final output) as well.


by above sample data now it should retrieve only employee-4 record.
Previous Topic: how to store the 15 digit decimal value ? (merged 4)
Next Topic: How to delete duplicate rows with reverse data
Goto Forum:
  


Current Time: Fri Dec 09 23:06:20 CST 2016

Total time taken to generate the page: 0.25915 seconds