Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sundaram123
Starting Member
15 Posts |
 Posted - 2004-07-02 : 15:06:22
|
| Hi,CREATE tABLE staff ( id int AUTO_INCREMENT PRIMARY KEY, name varchar(20), role varchar(20), managerid int);insert into staff values (1, 'Kumar', 'CEO', 0);insert into staff values (2, 'Mani', 'T.Manaeger', 1);insert into staff values (3, 'Sathis', 'CFO', 1);insert into staff values (4, 'Sundaram', 'Projet Manager', 2);insert into staff values (5, 'Valluvan', 'P.Leader', 4);insert into staff values(6, 'Jack', 'Developer',5);I have the above table and data; I want the query to display following outputHere is the condition1. Want to display all records2. Display Manger name3. Display Manager subordinate count ( number of people under that manager)4. Display Managers Manager idI need SQL query for this.ThanksSundaram======Output=========id name role Manager name Sub. count Managers Manager id1 Kumar CEO null 2 02 Mani T.Manaeger Kumar 1 03 Sathis CFO Kumar 0 04 Sundaram Projet Manager Mani 1 15 Valluvan P.Leader Sundaram 1 26 Jack Developer Valluvan 0 4===================Output with "|" delimiterid|name|role|Managername|Sub.count|Managers-Manager-id1|Kumar|CEO|null|2|02|Mani|T.Manaeger|Kumar|1|03|Sathis|CFO|Kumar|0|04|Sundaram|Projet|Manager|Mani|1|15|Valluvan|P.Leader|Sundaram|1|26|Jack|Developer|Valluvan|0|4 |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-07-02 : 15:27:15
|
| [code]select s1.name,s1.role, s2.name manager, isnull(sub_count,0) as sub_count, isnull(s3.managerid,0) man_man_idfrom staff s1left join staff s2 on s2.id = s1.manageridleft join staff s3 on s3.name = s2.nameleft join( select managerid, count(managerid) as sub_count from staff group by managerid) d on d.managerid = s1.id[/code] |
 |
|
|
|
|
|
|
|
SQL Query help