Home » SQL & PL/SQL » SQL & PL/SQL » problem writing in correlated subquery (ORACLE 10G)
problem writing in correlated subquery [message #324247] Sun, 01 June 2008 00:32 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
I have 2 two tables employee and employee_items.

1 employee can have multiple items.
In employee_items ref_no columm is there.this column data can be duplicated for the corresponding employee id in employee_items table;

i want to find out for howmany employee's are using same REF_NO for employee type="A";i have written correlated subquery.i think
its not returing correct results.


SELECT * FROM employee EMP
WHERE
EMP.TYPE='A'
AND exists
(
select 'X' FROM
employee_items EMPIT1,
employee_items EMPIT2,
employee EMP2
WHERE
EMPIT1.EMP_ID=EMP.ID
AND EMPIT1.REF_NO=EMPIT2.REF_NO
AND EMPIT2.EMP_ID=EMP2.ID
)



INSERT INTO EMPLOYEE(ID,TYPE) VALUES(1,'A');
INSERT INTO EMPLOYEE(ID,TYPE) VALUES(2,'B');
INSERT INTO EMPLOYEE(ID,TYPE) VALUES(3,'A');
INSERT INTO EMPLOYEE(ID,TYPE) VALUES(4,'A');
INSERT INTO EMPLOYEE(ID,TYPE) VALUES(5,'A');

INSERT INTO EMPLOYEE_items(ID,EMP_ID,REF_NO) VALUES(1,1,'AAA');
INSERT INTO EMPLOYEE_items(ID,EMP_ID,REF_NO) VALUES(2,1,'AAA');
INSERT INTO EMPLOYEE_items(ID,EMP_ID,REF_NO) VALUES(3,1,'CCC');


INSERT INTO EMPLOYEE_items(ID,EMP_ID,REF_NO) VALUES(4,2,'KKK');
INSERT INTO EMPLOYEE_items(ID,EMP_ID,REF_NO) VALUES(5,2,'CCC');


INSERT INTO EMPLOYEE_items(ID,EMP_ID,REF_NO) VALUES(6,3,'FFMM');
INSERT INTO EMPLOYEE_items(ID,EMP_ID,REF_NO) VALUES(7,3,'FFMM');
INSERT INTO EMPLOYEE_items(ID,EMP_ID,REF_NO) VALUES(8,3,'MM');

INSERT INTO EMPLOYEE_items(ID,EMP_ID,REF_NO) VALUES(9,4,'MMMM123');
INSERT INTO EMPLOYEE_items(ID,EMP_ID,REF_NO) VALUES(10,4,'FFMM');
INSERT INTO EMPLOYEE_items(ID,EMP_ID,REF_NO) VALUES(11,4,'MM');
INSERT INTO EMPLOYEE_items(ID,EMP_ID,REF_NO) VALUES(12,4,'');

INSERT INTO EMPLOYEE_items(ID,EMP_ID,REF_NO) VALUES(13,5,'MMMM');
INSERT INTO EMPLOYEE_items(ID,EMP_ID,REF_NO) VALUES(14,5,'XXX');
INSERT INTO EMPLOYEE_items(ID,EMP_ID,REF_NO) VALUES(15,5,'AAA');

[Updated on: Sun, 01 June 2008 00:34]

Report message to a moderator

Re: problem writing in correlated subquery [message #324262 is a reply to message #324247] Sun, 01 June 2008 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nice to have posted the insert statements but as you didn't post the table definition (or better create table statement), we don't what are the columns.

Anyway, to answer your question you just have to make a join and group by ref_no.

Regards
Michel
Re: problem writing in correlated subquery [message #324268 is a reply to message #324247] Sun, 01 June 2008 04:55 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
CREATE TABLE EMPLOYEE
(ID NUMBER PK,
TYPE VARCHAR2)

CREATE TABLE EMPLOYEE_ITEMS
(ID NUMBER PK,
EMP_ID NUMBER FK REFERENCES EMPLOYEE(ID),
REF_NO VARCHAR2)

u said make a join but already have a join
Re: problem writing in correlated subquery [message #324276 is a reply to message #324268] Sun, 01 June 2008 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I meant you don't need any subquery of any kind.

Regards
Michel
Re: problem writing in correlated subquery [message #324278 is a reply to message #324276] Sun, 01 June 2008 06:48 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
yes you are correct.but i want only Employee information in the select statement.as you said if i try to fetch employee information in the select statement by using join then whatver fields i am using in select statement ,same fields should be used in GROUP BY function.then resutls will be different.

IF I WRITE LIKE THIS then results will be different.si i have to use only correlated subquery.
SELECT empit1.EMP_ID, empit1.REF_NO FROM
EMPLOYEE emp,
EMPLOYEE_ITEMS empit1,
EMPLOYEE_ITEMS empit2
WHERE
empit1.EMP_ID=emp.ID
AND empit1.REF_NO=empit2.REF_NO
and emp.TYPE='A'
GROUP BY empit1.EMP_ID,empit1.REF_NO
Re: problem writing in correlated subquery [message #324282 is a reply to message #324278] Sun, 01 June 2008 08:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not what I suggested. You only need one instance of each table.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel

Re: problem writing in correlated subquery [message #324285 is a reply to message #324282] Sun, 01 June 2008 09:06 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
Michel Cadot wrote on Sun, 01 June 2008 18:48
This is not what I suggested. You only need one instance of each table.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel




I am new to this forum.next time onwards i will follow the rules.

can you rewrite the query? i didnt get what you said.
Re: problem writing in correlated subquery [message #324287 is a reply to message #324247] Sun, 01 June 2008 09:35 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
select ref_no, count(*)
from .....
Re: problem writing in correlated subquery [message #324349 is a reply to message #324287] Mon, 02 June 2008 02:08 Go to previous messageGo to next message
rawat_me
Messages: 45
Registered: September 2005
Member
The Query is :

select ref_no , count(*) from EMPLOYEE_items where ID IN ( select ID from employee where type = 'A' )
group by ref_no
Re: problem writing in correlated subquery [message #324351 is a reply to message #324349] Mon, 02 June 2008 02:22 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@rawat_me,

Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Also please read "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Mon, 02 June 2008 02:22]

Report message to a moderator

Previous Topic: PL/block not working
Next Topic: Information:How project database with Oracle to send on a server
Goto Forum:
  


Current Time: Sat Dec 03 13:45:36 CST 2016

Total time taken to generate the page: 0.09921 seconds