Home » SQL & PL/SQL » SQL & PL/SQL » Help Needed with a subquery
Help Needed with a subquery [message #264171] Sat, 01 September 2007 06:23 Go to next message
ashwin1985
Messages: 4
Registered: September 2007
Junior Member
Can someone help me with this query..I dont know whether its simple or not as i'm just a beginer in oracle. I would like to explain what i need through emp table so that it would be easy to relate to.

I need empno,ename,deptno from emp where deptno=40 and use the empno from the pervious query to search for other rows in the table and if it matches it should check for the condition deptno=50 or 60.

Awaiting all your replies
Thank you
Re: Help Needed with a subquery [message #264173 is a reply to message #264171] Sat, 01 September 2007 06:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
empno is the primary key of emp table, so no other row will match.
But if he could be, just join the table emp with itself (self join).
See Joins.

Regards
Michel
Re: Help Needed with a subquery [message #264179 is a reply to message #264171] Sat, 01 September 2007 08:37 Go to previous messageGo to next message
debasisdas
Messages: 2
Registered: August 2007
Location: Bangalore
Junior Member

You need to use Join to retrive the desired records.
Re: Help Needed with a subquery [message #264185 is a reply to message #264179] Sat, 01 September 2007 09:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks to (weakly) confirm what I said.

Regards
Michel
Re: Help Needed with a subquery [message #264239 is a reply to message #264171] Sun, 02 September 2007 05:16 Go to previous messageGo to next message
ashwin1985
Messages: 4
Registered: September 2007
Junior Member
Thanks for all.. Just assume that there is no primary key in this table.

If i use a self join my query would be

select a.empno,a.ename,a.deptno from emp a where a.empno in (select b.empno from emp b where b.deptno in (50,60))


In this, how do i check for the first condition which says deptno should be 40.

Could anyone help me again..
Re: Help Needed with a subquery [message #264244 is a reply to message #264171] Sun, 02 September 2007 06:08 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Quote:

select a.empno,a.ename,a.deptno from emp a where a.empno in (select b.empno from emp b where b.deptno in (50,60))



Add and after your code deptno = 40. But May be no record will return because your subquery return those empno which have deptno in 50 and 60.
select a.empno,a.ename,a.deptno from emp a where a.empno in (select b.empno from emp b where b.deptno in (50,60)) 
and a.deptno= 40
Re: Help Needed with a subquery [message #264264 is a reply to message #264239] Sun, 02 September 2007 09:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is the opposite: you first search empno with deptno=40, so this must be in the inner query.
Then you find the corresponding empno with deptno in (50,60), so this must be in the outer query.

Now the way you express it depends on the final result you want.
Do you want to display the data from the first query or only those from the second one?
Do you want to display the data from the first query, even if there are no corresponding rows for the second one?
...

Regards
Michel
Re: Help Needed with a subquery [message #264412 is a reply to message #264264] Mon, 03 September 2007 04:11 Go to previous messageGo to next message
ashwin1985
Messages: 4
Registered: September 2007
Junior Member
Michel Cadot wrote on Sun, 02 September 2007 09:50
It is the opposite: you first search empno with deptno=40, so this must be in the inner query.
Then you find the corresponding empno with deptno in (50,60), so this must be in the outer query.

Now the way you express it depends on the final result you want.
Do you want to display the data from the first query or only those from the second one?
Do you want to display the data from the first query, even if there are no corresponding rows for the second one?
...

Regards
Michel


No i want to display data from the first query only if the empno with deptno 40 also has deptno 50 or 60. How should my query look like. Moreover how to first check for the deptno = 40 and then for the other two conditions.

Will this work
select a.empno,a.ename,a.deptno from emp a where a.deptno in (50,60) and a.empno in (select b.empno from emp b where b.deptno =40 )

Is there any other way i could write this query without using a subquery.

[Updated on: Mon, 03 September 2007 04:17]

Report message to a moderator

Re: Help Needed with a subquery [message #264418 is a reply to message #264412] Mon, 03 September 2007 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
No i want to display data from the first query only if the empno with deptno 40 also has deptno 50 or 60

This is not what your query returns. It returns data from second query (those with dept 50 or 60) and from the first one (those with dept 40.
Quote:
Is there any other way i could write this query without using a subquery.

Many.

Regards
Michel
Re: Help Needed with a subquery [message #264522 is a reply to message #264418] Mon, 03 September 2007 11:28 Go to previous messageGo to next message
ashwin1985
Messages: 4
Registered: September 2007
Junior Member
Michel Cadot wrote on Mon, 03 September 2007 04:42

Quote:
Is there any other way i could write this query without using a subquery.

Many.

Regards
Michel



Could please list some of the ways so that i need not use a subquery and learn from this. Hope you wont mind. Thanks for your help. Hoping to hear from you soon.

[Updated on: Mon, 03 September 2007 11:57] by Moderator

Report message to a moderator

Re: Help Needed with a subquery [message #264532 is a reply to message #264522] Mon, 03 September 2007 11:58 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When I said many this is really many, so no I can't write all.
Find them.

Regards
Michel
Previous Topic: indexes
Next Topic: grant option does not exist
Goto Forum:
  


Current Time: Sun Dec 04 08:57:26 CST 2016

Total time taken to generate the page: 0.07257 seconds