My Query - 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 2000 Forums

SQL Server Development (2000)

My Query

Author

Topic

Jaswinder
Starting Member

8 Posts

Posted - 2007-04-23 : 03:45:45

Staff_ID Fname MgrId
1 Jaswinedr 4
2 Alex 4
3 Vikas 4
4 Deepika 5
5 Manoj null

Query : If @MgrId is supplied then bring the data from above table such that if @Mgrid is 4 then result should be

the content of the table can nest to what so ever level but it is to bring all the nested related data ,, plz help

Jaswinder , Alex , Vikas

but if @Mgrid is 5 then the result should be

Jaswinder , Alex , Vikas , Deepika

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-23 : 03:49:45

[code]
declare @fname varchar(4000)

select @fname = isnull(@fname + ' , ', '') + Fname
from yourtable
where MgrId <= 4
order by Staff_ID

print @fname
[/code]

Jaswinder
Starting Member

8 Posts

Posted - 2007-04-23 : 04:34:24

Hi

Thanks for your reply but the data is not always to compare less than the supplied value , it can be lesser or greater or combination of both , i guess it will work using self join , plz help me !

quote:

Originally posted by khtan


declare @fname	varchar(4000)

select	@fname = isnull(@fname + ' , ', '') + Fname
from	yourtable
where	MgrId	<= 4
order by Staff_ID

print	@fname

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-23 : 04:36:56

Unless you show us sample data and expected output which describes your requirement clearly, we can't help you.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Jaswinder
Starting Member

8 Posts

Posted - 2007-04-23 : 04:56:48

Staff_ID Fname MgrId
1 Deepika 3
2 Jaswinder 1
3 Manoj null
4 Vikas 1
5 Alex 1
6 Sandeep 3
7 Shanti 3
8 Meenu 1
9 Chavi 1
10 Sahil 1
11 Ashish 1
12 Arvind 1
13 Sunny 6

This table is created to maintain hierachy of managers
there can be a hierachy and there cannot be , my problem is to retrive Fnames from above table which are assigned some mannager .,for example
If @MgrId=6 then the result sould be sunny

NOte :-- there cana be a situation of a 2 or more level hierachy for example
if @MgrId=3 then the result sould be Deepika,Sandeep,Shanti also their nested values ie jaswinder,Vikas,Alex,Meenu,Chavi,Sahil,ashish ,Arvind

ie it should check for all the nested data within the same table.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-23 : 05:07:29

[url]http://www.nigelrivett.net/SQLTsql/RetrieveTreeHierarchy.html[/url]
[url]http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Jaswinder
Starting Member

8 Posts

Posted - 2007-04-23 : 05:48:57

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources