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  |
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 #357090 is a reply to message #357089] |
Mon, 03 November 2008 20:52   |
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  |
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.
|
|
|
Goto Forum:
Current Time: Sat Feb 08 13:17:43 CST 2025
|