By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,334 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,334 IT Pros & Developers. It's quick & easy.

Simple Query

P: n/a
Hi,
Need a query which satisfies the following condition ::

I have a table with fields

emp_no emp_name supervisor
111 harry 222
222 potter null
When I give an emp_no( For eg 111) the output should be

emp_name Supervisor
harry potter

When I give an emp_no( 222) the output should be

emp_name Supervisor
potter null
Thanks in advance

Mar 23 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
"rAinDeEr" <ta**********@gmail.com> wrote in message
news:11*********************@v46g2000cwv.googlegro ups.com...
Hi,
Need a query which satisfies the following condition ::

I have a table with fields

emp_no emp_name supervisor
111 harry 222
222 potter null
When I give an emp_no( For eg 111) the output should be

emp_name Supervisor
harry potter

When I give an emp_no( 222) the output should be

emp_name Supervisor
potter null
Thanks in advance


select
a.emp_name,
(select b.emp_name from emp_table b where a.supervisor = b.emp_no) as
supervisor
from emp_table a
where emp_no = 111
Mar 23 '06 #2

P: n/a
Thanks..Mark..Actually i was trying in Mysql and its not working but
your code worked in Db2 UDB

~ Me

Mar 23 '06 #3

P: n/a
Another way is using outer join.

select
a.emp_name, b.emp_name as supervisor
from emp_table a
left outer join
emp_table b
on a.supervisor = b.emp_no
where a.emp_no = 111

Mar 23 '06 #4

P: n/a
rAinDeEr wrote:
Thanks..Mark..Actually i was trying in Mysql and its not working but
your code worked in Db2 UDB


You might need recursive queries if you want to consider more than just one
level in the hierarchy.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Mar 23 '06 #5

P: n/a
Hi Knut Stolze,

Am not aware of recursive queries..
Can you show me some examples or point me to some interesting
articles..

Think that if some one can illustrate it would be so helpful..

~ Me

Mar 24 '06 #6

P: n/a
rAinDeEr wrote:
Hi Knut Stolze,

Am not aware of recursive queries..
Can you show me some examples or point me to some interesting
articles..

Think that if some one can illustrate it would be so helpful..


Have a look here:
http://publib.boulder.ibm.com/infoce...n/r0000879.htm

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Mar 24 '06 #7

P: n/a
Knut Stolze wrote:
rAinDeEr wrote:
Hi Knut Stolze,

Am not aware of recursive queries..
Can you show me some examples or point me to some interesting
articles..

Think that if some one can illustrate it would be so helpful..
Have a look here:

http://publib.boulder.ibm.com/infoce...n/r0000879.htm


Search for "bill of materials" or "recursion" there.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Mar 24 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.