Home » SQL & PL/SQL » SQL & PL/SQL » how to write this query
how to write this query [message #356914] Mon, 03 November 2008 02:08 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
One EMPLOYEE can have multiple EMPLOYEE_PACKS.each pack must have one or more items.

if you see data below for 1st employee 4 packs are there and for 3rd and 6th packs doesn't have items in the ITEMS table.
for 2nd employee 2 packs are there.for each pack one item is there.for 3rd employee also having 1 pack and 1 item.

i want to fetch the only employee information which does not have any item for the corresponding pack.below 1st query fetches first EMP id;that is fine.

see the next query i want to fetch the employee which is having atleast one item for the respective pack.my assumption is that it should fetch 2nd employee and 3rd employee record only but its fetching all employees.i think i am missing something in the correlated subqueries.

both 1st and 2nd quieries almost similiar just i put 1 > for 1st query and the other one is 0 < ;.but why 1st query fetching correct record and the another query fetching all records.this is my confusion.....



can i put EMPLOYEE_PACKS in the exists clause and achive the result instead of putting in the main select statement?






1st query
********
 
SELECT EMP.ID,EMP.EMPNO FROM EMPLOYEE EMP , EMPLOYEE_PACKS EMPK 
        WHERE 
EMP.ID=EMPK.EMP_ID 
AND        
1 > ( SELECT COUNT(*) 
FROM EMPLOYEEPK_ITEMS EMPIT 
WHERE EMPIT.EMPPK_ID=EMPK.ID
)
GROUP BY EMP.ID,EMP.EMPNO;
 
2nd query
********
 
SELECT EMP.ID,EMP.EMPNO FROM EMPLOYEE EMP , EMPLOYEE_PACKS EMPK 
        WHERE 
EMP.ID=EMPK.EMP_ID 
AND        
0 <  ( SELECT COUNT(*) 
FROM EMPLOYEEPK_ITEMS EMPIT 
WHERE EMPIT.EMPPK_ID=EMPK.ID
)
GROUP BY EMP.ID,EMP.EMPNO;
 
 
 
 
 
 
CREATE TABLE EMPLOYEE
(
  ID			NUMBER NOT NULL ,
  EMPNO          VARCHAR2(10)
 
);
 
alter table EMPLOYEE
  add constraint EMP_PK primary key (ID);
  
INSERT INTO EMPLOYEE(ID,EMPNO) VALUES(1,'EMP1');
INSERT INTO EMPLOYEE(ID,EMPNO) VALUES(2,'EMP2');
INSERT INTO EMPLOYEE(ID,EMPNO) VALUES(3,'EMP3');
 
create table EMPLOYEE_PACKS
(
  ID     NUMBER not null,
  EMP_ID NUMBER
);
 
alter table EMPLOYEE_PACKS
  add constraint EMPCS_PK primary key (ID);
 
alter table EMPLOYEE_PACKS
  add constraint EMPCS_FK foreign key (EMP_ID)
  references EMPLOYEE (ID);
 
INSERT INTO EMPLOYEE_PACKS(ID,EMP_ID) VALUES(1,1);
INSERT INTO EMPLOYEE_PACKS(ID,EMP_ID) VALUES(2,1);
INSERT INTO EMPLOYEE_PACKS(ID,EMP_ID) VALUES(3,1);
 
INSERT INTO EMPLOYEE_PACKS(ID,EMP_ID) VALUES(4,2);
INSERT INTO EMPLOYEE_PACKS(ID,EMP_ID) VALUES(5,2);
INSERT INTO EMPLOYEE_PACKS(ID,EMP_ID) VALUES(6,1);
INSERT INTO EMPLOYEE_PACKS(ID,EMP_ID) VALUES(7,3);
 
 
 
CREATE TABLE EMPLOYEEPK_ITEMS
(
  ID                   NUMBER NOT NULL,
  EMPPK_ID             NUMBER NOT NULL,
  COUNTRY              VARCHAR2(10)    
);
 
alter table EMPLOYEEPK_ITEMS
  add constraint EMPCSITEM_PK primary key (ID);
alter table EMPLOYEEPK_ITEMS
  add constraint EMPCSITEM_FK foreign key (EMPPK_ID)
  references EMPLOYEE_PACKS (ID);
 
INSERT INTO EMPLOYEEPK_ITEMS(ID,EMPPK_ID,COUNTRY) VALUES(1,1,'AAA');
INSERT INTO EMPLOYEEPK_ITEMS(ID,EMPPK_ID,COUNTRY) VALUES(2,2,'SSS');
 
INSERT INTO EMPLOYEEPK_ITEMS(ID,EMPPK_ID,COUNTRY) VALUES(3,4,'FFF');
INSERT INTO EMPLOYEEPK_ITEMS(ID,EMPPK_ID,COUNTRY) VALUES(4,5,'XXX');
INSERT INTO EMPLOYEEPK_ITEMS(ID,EMPPK_ID,COUNTRY) VALUES(5,7,'ccccc');


[Updated on: Mon, 03 November 2008 02:12]

Report message to a moderator

Re: how to write this query [message #357073 is a reply to message #356914] Mon, 03 November 2008 15:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Emp1 has pack 1 which has item AAA and pack 2 which has item SSS, so Emp1 should also be in your result set, so you are getting the correct result. However, a correlated subquery is a slow way to get your results if you are selecting from tables with a lot of rows in them. A better method would be to join all three tables and use a HAVING clause instead of a correlated subquery.
Re: how to write this query [message #357088 is a reply to message #357073] Mon, 03 November 2008 19:56 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
i didnt understand much.can you show the query what u have explained?
Re: how to write this query [message #357089 is a reply to message #357088] Mon, 03 November 2008 20:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
What I am suggesting is that instead of using a correlated subquery like this:

SCOTT@orcl_11g> SELECT dept.deptno, dept.dname
  2  FROM   dept
  3  WHERE  0 <
  4  	    (SELECT COUNT (*)
  5  	     FROM   emp
  6  	     WHERE  emp.deptno = dept.deptno)
  7  ORDER  BY deptno
  8  /

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES

SCOTT@orcl_11g> 


it is faster to use a join and a having clause like this:

SCOTT@orcl_11g> SELECT dept.deptno, dept.dname
  2  FROM   dept, emp
  3  WHERE  emp.deptno = dept.deptno
  4  GROUP  BY dept.deptno, dept.dname
  5  HAVING COUNT (*) > 0
  6  ORDER  BY deptno
  7  /

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES

SCOTT@orcl_11g> 

Re: how to write this query [message #357090 is a reply to message #357089] Mon, 03 November 2008 20:52 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
Please read the Q carefully.i want only emp information not child information.

if u see the data emp1 has 4 packs.
3rd and 6th packs doesn't have items for this employee(emp1).

if u see the data emp2 &emp3 and these employees having packs and eack pack having item in the items table.

so in final result Below employees should be displayed .

2 EMP2
3 EMP3
Re: how to write this query [message #357093 is a reply to message #357090] Mon, 03 November 2008 21:52 Go to previous message
bonker
Messages: 402
Registered: July 2005
Senior Member
I could not test this but I guess the query should be some thing like this

Select
EMP.ID,
EMP.EMPNO 
FROM EMPLOYEE EMP 
where EMP.ID not in 
              (select nvl(emp_pack.EMP_ID,0) 
                     from EMPLOYEE_PACKS emp_pack  
                     where  emp_pack.ID not in 
                            (select EMPPK_ID     
                                   from  EMPLOYEEPK_ITEMS)
               )


Please note the above code is not tested.
Previous Topic: Tables not updating... even after commit.
Next Topic: Pl Sql - Error
Goto Forum:
  


Current Time: Thu Dec 08 20:10:13 CST 2016

Total time taken to generate the page: 0.08922 seconds