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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

ArnoldG
Starting Member

36 Posts

Posted - 2014-03-20 : 08:05:18
Thanks a lot Stepson...
This works perfectly !
Arnold
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-20 : 08:09:01
your welcome!


sabinWeb MCP
Go to Top of Page
   

- Advertisement -