Simple Query

rAinDeEr

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

Mark 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

rAinDeEr

Thanks..Mark..Actually i was trying in Mysql and its not working but
your code worked in Db2 UDB

~ Me

Mar 23 '06 #3

Tonkuma

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

Knut Stolze

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

rAinDeEr

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

Knut Stolze

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

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