Simple Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rAinDeEr - 23 Mar 2006 07:45 GMT

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

Reply to this Message

Mark A - 23 Mar 2006 08:40 GMT

> Hi,
> Need a query which satisfies the following condition ::
[quoted text clipped - 16 lines]
>
> 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

Reply to this Message

rAinDeEr - 23 Mar 2006 10:07 GMT

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

~ Me

Reply to this Message

Tonkuma - 23 Mar 2006 13:02 GMT

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

Reply to this Message

Knut Stolze - 23 Mar 2006 21:36 GMT

> 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.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

rAinDeEr - 24 Mar 2006 04:16 GMT

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

Reply to this Message

Knut Stolze - 24 Mar 2006 11:08 GMT

> Hi Knut Stolze,
>
[quoted text clipped - 3 lines]
>
> Think that if some one can illustrate it would be so helpful..

Have a look here:
http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/ad
min/r0000879.htm

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Knut Stolze - 24 Mar 2006 11:17 GMT

>> Hi Knut Stolze,
>>
[quoted text clipped - 5 lines]
>
> Have a look here:

http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/ad
min/r0000879.htm

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

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message