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 |
rischfre
Starting Member
15 Posts |
Posted - 2013-05-22 : 06:20:16
|
Hiwe describe each Job in a table. This table is almost "circular" as one of the key referes to the same table which represents the responsable of each position.T_Jobs id_job the KEY JobDescription the Description Pid the KEY of the Boss of this JobNow I would like to do a SELECT which returns: id_job, jobdescription, jobdescription of his boss, and what i do not know how to managed it : at which level is its job.Level is defined this way:the CEO (the only with PID = 0) has level 1the directors (PID = "ID of CEO") have level 2the managers (PID = "ID of the Directors) have level 3....How do i get this circular value "Level"?thank you, best regards |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-05-22 : 06:37:13
|
Refer this link http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx--Chandu |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-05-22 : 06:50:12
|
[code]--T_Jobs--id_job the KEY--JobDescription the Description--Pid the KEY of the Boss of this Job--Now I would like to do a SELECT which returns: id_job, jobdescription, jobdescription of his boss, and what i do not know how to managed it : at which level is its job.--Level is defined this way:--the CEO (the only with PID = 0) has level 1--the directors (PID = "ID of CEO") have level 2--the managers (PID = "ID of the Directors) have level 3--....--How do i get this circular value "Level"?-- testdatadeclare @T_Jobs table ( id_job int, Job_Description varchar(255), Pid int )insert @T_Jobsselect 1, 'CEO', null union allselect 245, 'Director X', 1 union allselect 210, 'Director Y', 1 union allselect 105, 'Manager A', 245 union allselect 420, 'Manager D', 210 union allselect 700, 'Manager S', 245-- show testdataselect * from @T_Jobs-- solution;WITH JobCTEAS( SELECT id_job, Job_Description, convert(varchar(255),'') as Job_Description_of_Boss, 1 AS Job_level FROM @T_Jobs WHERE Pid is null UNION ALL SELECT T.id_job, T.Job_Description, A.Job_Description as Job_Description_of_Boss, A.Job_level + 1 FROM @T_Jobs T JOIN JobCTE A ON T.Pid = A.id_job)SELECT *FROM JobCTE[/code] Too old to Rock'n'Roll too young to die. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-22 : 06:51:54
|
use recursive cte approachwould be like;With JobHierarchyAS(--Getting first (root) levelSELECT *,CAST(JobDescription AS varchar(max)) AS JobPathFROM T_JobsWHERE PID = 0UNION ALLSELECT t.*, CAST(j.JobPath + '/' + t.JobDescription AS varchar(max)) FROM JobHierarchy jINNER JOIN T_Jobs tOn t.PID = j.id_job)SELECT id_job, JobDescription, JobPathFROM JobHierarchyOPTION (MAXRECURSION 0) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
rischfre
Starting Member
15 Posts |
Posted - 2013-05-22 : 09:31:02
|
Thank you very much thants workign very well |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-23 : 00:55:37
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-05-23 : 03:02:53
|
welcome Too old to Rock'n'Roll too young to die. |
|
|
|
|
|
|
|