467,189 Members | 1,264 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,189 developers. It's quick & easy.

Simple Query

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
  • viewed: 1253
Share:
7 Replies
"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
Thanks..Mark..Actually i was trying in Mysql and its not working but
your code worked in Db2 UDB

~ Me

Mar 23 '06 #3
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
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
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
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
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.

Similar topics

reply views Thread by unixman | last post: by
6 posts views Thread by Eddie Smit | last post: by
3 posts views Thread by Don Sealer | last post: by
2 posts views Thread by Don Wash | last post: by
3 posts views Thread by John Baker | last post: by
27 posts views Thread by one man army | last post: by
2 posts views Thread by Fendi Baba | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.