SEO

#1 (**permalink**) 02-27-2008, 06:29 AM

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

#2 (**permalink**) 02-27-2008, 06:29 AM

"rAinDeEr" <tariqrahiman@gmail.com> wrote in message
news:1143096329.893735.40360@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

#3 (**permalink**) 02-27-2008, 06:29 AM

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

~ Me

#4 (**permalink**) 02-27-2008, 06:29 AM

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

#5 (**permalink**) 02-27-2008, 06:29 AM

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

#6 (**permalink**) 02-27-2008, 06:30 AM

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

#7 (**permalink**) 02-27-2008, 06:30 AM

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

#8 (**permalink**) 02-27-2008, 06:30 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Simple Query