SQL

[Manish]

Friends
I have have a table named Employee...which contains information abt the employee and his/her immediate boss. and i want to findout all the employees working under any given BOSS..directly or indirectly. i don't want to use procedured....isit possible?????

[ganraj]

Hello,


Write a SQL like this....

Dim SQLSQL as String
Dim BOSSNAME as String
Dim Myset as Recordset

BOSSNAME="BIG BOSS"

SQLSQL="SELECT * FROM EMPLOYEE "
SQLSQL=SQLSQL+" WHERE EMPLOYEE.BOSS='" & BOSSNAME & "'

Set Myset=Database.OpenRecordset(SQLSQL)


Email me if you have further...



------------------
ganraj.

[Manish]

HI
i am nt talking abt accessing the data in VB application. What i am talking abt is let say i hv this structure
id ename boss
1 ABC 0
2 AAA 0
3 XXX 1
4 AXR 3
5 BBB 4

so what i want is when i specify id "1" it shld gv me following output
id ename boss
3 XXX 1
4 AXR 3
5 BBB 4

and i don't want to use any programming... i want to write plain sql script
Thanx Manish

[Clunietp]

Select * from mytable where bossid >= 1 order by bossid

I don't think this is what you are looking for.... can you be more specific?

Tom

[This message has been edited by Clunietp (edited 10-26-1999).]

[Manish]

the script u hv given wouldnt solve the case. What i want is Let say there is boss A whoc is looking after 2 departments, these departments are managed by two jr boss an dsome other employees aare wrking under them also. so whn i say that i need a list of allthe employees working under BOSS A it should give me list of which includes boss of these two department as well as all staff working in those departments. bcz those employees also working under boss A Indirectly

Thanx


------------------

[Clunietp]

I don't know if you could do it in plain SQL w/o any code, you may have to normalize your database and use joins

[JohnAtWork]

I hate to tell you this, but ANSI std SQl has no way to do this.


What you're looking for is called a hierarchical query and there is no ANSI Std answer for it at this moment...

Oracle, however, does have an answer for it, if you're interested I could post the SQL for it.

In VB, I'm afraid you will have to use some kind of code to execute this loop.

[Manish]

hi
Yes i would like to know how u can achive this in oracle. pls if u could send me the codes.
Thanx Manish