Child projects - 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 2012 Forums

Transact-SQL (2012)

Child projects

Author

Topic

ArnoldG
Starting Member

36 Posts

Posted - 2014-03-20 : 06:57:13

Hello, who can help me with this ?

I have got 2 database tables:

Table “Projects”, with parent projects and child projects
Joined to table
“ProjectMembers”, with employee ID’s.

I want project members that are in the parent project table to be able to be added in the results of a child project without having to be added as a member of each of the child projects.
So as long you are a member of the parent project you also get the results of the child project.


SELECT 
	 Pr.ProjectNr
	,Pr.ParentProject
	,pm.Emp_id
	 
FROM
	prproject Pr
	LEFT JOIN dbo.prmember Pm	
		ON Pr.ProjectNr = Pm.ProjectNr

WHERE 
	(Pr.projectnr = '2014055' 
		OR Pr.parentproject = '2014055'
	)


Result set:
	ProjectNr     ParentProject  Emp_Id
1	2014055       NULL	        5
2	2014055       NULL	        21
3	2014055.001   2014055           5

Emp_id is a member of the parent project AND the child project, bit Emp_id is only a member of the parent project.
So I would like Emp_ID 21 to be added in the results of project 2014055.001

How would I do that ?

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-20 : 07:14:31

[code]

;with aCTE
AS
(Select '2014055' as ProjectNr,Null as ParentProject, 5 as Emp_ID union all
select '2014055' , null, 21 union all
select '2014055.001' ,2014055, 5)

select
A.ProjectNr
,A.ParentProject
,A.Emp_ID
,STUFF(C.Emp_IDs,1,1,'') as Emp_lst
from
aCTE as A
outer apply
(
select ', ' + Cast(Emp_ID as varchar(30))
from aCTE as B
where ParentProject is null
AND CAST(A.ParentProject as varchar(30))= CAST(B.ProjectNr as varchar(30))
for xml path('')) C(Emp_IDs)

[/code]

output

[code]
ProjectNr ParentProject Emp_ID Emp_lst
2014055 NULL 5 NULL
2014055 NULL 21 NULL
2014055.001 2014055 5 5, 21
[/code]

sabinWeb MCP

ArnoldG
Starting Member

36 Posts

Posted - 2014-03-20 : 07:26:16

Thanks for your help Stepson...
Actually my target was to get Emp_ID 21 added in a separate row, because Emp_id 21 is member of the parent project:


The target result set would then be:
	ProjectNr     ParentProject  Emp_Id
1	2014055       NULL	        5
2	2014055       NULL	        21
3	2014055.001   2014055           5
4       2014055.001   2014055           21

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-20 : 07:32:20

[code]

; with prproject
AS
(select '2014055' as ProjectNr, null as ParentProject union all
select '2014055.001','2014055')

, prmember
AS
(select '2014055' as ProjectNr, 5 as Emp_ID union all
select '2014055' , 21 union all
select '2014055.001',5)

SELECT
Pr.ProjectNr
,Pr.ParentProject
,pm.Emp_id
,C.Emp_IDs as Emp_lst
FROM
prproject Pr
LEFT JOIN prmember Pm
ON Pr.ProjectNr = Pm.ProjectNr

outer apply
(
select Emp_ID
from
prmember Pm
where
CAST(Pr.ParentProject as varchar(30))= CAST(Pm.ProjectNr as varchar(30))
) C(Emp_IDs)

WHERE
(Pr.projectnr = '2014055'
OR Pr.parentproject = '2014055'
)

[/code]

output
[code]
ProjectNr ParentProject Emp_id Emp_lst
2014055 NULL 5 NULL
2014055 NULL 21 NULL
2014055.001 2014055 5 5
2014055.001 2014055 5 21
[/code]

sabinWeb MCP

ArnoldG
Starting Member

36 Posts

Posted - 2014-03-20 : 08:05:18

Thanks a lot Stepson...
This works perfectly !
Arnold

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-20 : 08:09:01

your welcome!

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources