help for table structure.. - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help for table structure..

Author

Topic

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-02-20 : 04:04:26

hi all....
I am here again to gain some help from you people...
Suppose there is a main table named as CEO ,under that CEo some managers are present,under managers there are some zone managers and under them there are some clerks..Means there are 4 levels in all,from top to bottom...Now i want to design table for this information and there have to be no primary keys to be included...
Also i have to calculate number of total persons under each level head..

Output should be as ..NAME HEAD
CEO NUll
MNGR1 CEO
Mngr2 CEO
ZoneManager1 Mngr1
ZoneManager2 Mngr1
ZoneManager3 Mngr2
Clerk1 ZoneManager2
Clerk1 ZoneManager2
Clerk2 ZoneManager3

Please suggest me how to make tables according to the data and output desired??

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-20 : 04:10:38

try like this
create table managers( id int identity(1,1),name varchar(32), parentid int)
insert into managers select 'ceo',null
insert into managers select 'mngr1',1
insert into managers select 'mngr2',1
insert into managers select 'ZoneManager1',2

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-02-20 : 04:22:11

m sorry for the mistake....but the repitition be occured due to some server problem....

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-20 : 04:25:16

i think this is ur requirement


declare @managers table( id int identity(1,1),name varchar(32), parentid int)
insert into @managers select 'ceo',null
insert into @managers select 'mngr1',1
insert into @managers select 'mngr2',1
insert into @managers select 'ZoneManager1',2
insert into @managers select 'ZoneManager2',2
insert into @managers select	'ZoneManager3',3
insert into @managers select 'Clerk1',5
insert into @managers select 'Clerk1',5
insert into @managers select 'Clerk2',6

select l.name,s.name as parentname
from @managers l
left join
@managers s on s.id = l.parentid

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-02-20 : 04:33:36

Now continuing my problem....
what if i have to count the levels in the table according to the data??
and how to count how many people in every individual level??
How to do this??

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-20 : 04:42:20

[code]
declare @managers table( id int identity(1,1),name varchar(32), parentid int)
insert into @managers select 'ceo',null
insert into @managers select 'mngr1',1
insert into @managers select 'mngr2',1
insert into @managers select 'ZoneManager1',2
insert into @managers select 'ZoneManager2',2
insert into @managers select 'ZoneManager3',3
insert into @managers select 'Clerk1',5
insert into @managers select 'Clerk1',5
insert into @managers select 'Clerk2',6

;with cte(id,name,parentid,parentname,level)
as(
select id,name,parentid,name,0 as level
from @managers where parentid is null

union all
select m.id,m.name,m.parentid,c.name,level+1
from @managers m
inner join cte c on c.id = m.parentid
)
select name,parentname,level from cte
[code]

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-02-20 : 05:01:55

thanks a lot bklr....
your suggestions have so far solved my query,will come back to you after some more enhancements....thanks a lot again...:)

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-02-20 : 05:06:58

Further my problem is to calsulate the numbers of persons below one manager...for example how many persons are present under mngr1??
or how many clerks are present under zonemanage21??
Hope you understand my query....

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-20 : 05:07:26

quote:
Originally posted by abcd

thanks a lot bklr....
your suggestions have so far solved my query,will come back to you after some more enhancements....thanks a lot again...:)

ur welcome

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-02-20 : 05:11:23

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-02-20 : 05:12:07

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-20 : 05:19:39

use this after cte
select name,parentname,level
from cte where parentid = 5 (give ur desired parentid) the u will get the name of the managers working under them

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-02-20 : 05:27:01

thanking you again bklr...:):)
will be back again...

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-20 : 05:37:52

quote:
Originally posted by abcd

thanking you again bklr...:):)
will be back again...

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources