i have 3 tables: 2 hierarchical tables , 1 junction between these.
teams id idparent 1 null 2 1 3 null 4 null 5 4 projects id idparent 1 null 2 null 3 2 4 2 5 null teamprojects idteam idproject 2 2 3 1 5 5 a project depend on @ least 1 team, teamprojects for.
the result i'm trying achieve: each of object (both teams , projects), want know ascendant , descendant objects (id concatened)
idobject ascendantteams descendantteam ascendantprojects descendantprojects 1 2 7, 10 2 1 7 3 6 4 5 4 10 6 3 7 2 8, 9 8 2 7 9 2 7 10 5 i trying achieve linq entities query, need both cte (for recursive part) , (stuff-for-xml) concatenation part... , neither translate linq entities. i'm trying make view help, dont manage write sql either.
how resolve this, either linq entities or sql?
what have not clear.
so, have made assumptions in order come solution. have assumed project included in descendantprojects team-based result ( see dbo.getprojectsforteam), , team included in descendantteams project-based result( see dbo.getteamsforproject). not @ clear question.
you need create scalar-valued functions achieve scenario.
how scalar valued functions called
each of these functions takes 2 parameters - type parameter a ascendants or d descendants, , id team id team-based query or project id project-based query.
team related scalar valued functions
create function [dbo].[getteamsforteam]( @teamtype varchar(20) , --either 'a' ascendants or 'd' descendants @teamid int) returns varchar(max) begin declare @list varchar(max); if @teamtype = 'a' begin --get parent teams select @list = coalesce(@list+','+cast(t.id varchar(10)) , cast(t.id varchar(10))) dbo.teams t t.id in ( select t2.idparent dbo.teams t2 t2.id = @teamid ); end; else begin --get children teams including @teamid select @list = coalesce(@list+','+cast(t.id varchar(10)) , cast(t.id varchar(10))) dbo.teams t t.id in ( select t2.id dbo.teams t2 t2.idparent = @teamid or t2.id = @teamid ); end; return @list; end; go create function [dbo].[getprojectsforteam]( @projecttype varchar(1) , --either 'a' ascendants or 'd' descendants @teamid int) returns varchar(max) begin declare @projects varchar(max); if @projecttype = 'a' begin --get projects parents of @teamid select @projects = coalesce(@projects+','+cast(tp.idproject varchar(10)) , cast(tp.idproject varchar(10))) dbo.teamprojects tp tp.idteam in ( select t.idparent dbo.teams t t.id = @teamid ); end; else begin --get projects children of @teamid including @teamid select @projects = coalesce(@projects+','+cast(tp.idproject varchar(10)) , cast(tp.idproject varchar(10))) dbo.teamprojects tp tp.idteam in ( select t.id dbo.teams t t.idparent = @teamid or t.id = @teamid ); end; return @projects; end; go using above scalar-valued functions, team-based query achieve final result set below.
select t.id teamid, dbo.getteamsforteam('a', t.id) ascendantteams, dbo.getteamsforteam('d', t.id) descendantteams, dbo.getprojectsforteam('a', t.id) ascendantprojects, dbo.getprojectsforteam('d', t.id) descendantprojects teams t; project related scalar valued functions
create function [dbo].[getteamsforproject] ( @teamtype varchar(20) , --either 'a' ascendants or 'd' descendants @projectid int ) returns varchar(max) begin declare @list varchar(max); if @teamtype = 'a' begin --get parent teams select @list = coalesce(@list+','+cast(t.id varchar(10)) , cast(t.id varchar(10))) dbo.teams t t.id in ( select t2.idparent dbo.projects p inner join dbo.teamprojects tp on p.id = tp.idproject inner join dbo.teams t2 on t2.id = tp.idteam p.id = @projectid ); end; else begin --get children teams including team @projectid select @list = coalesce(@list+','+cast(t.id varchar(10)) , cast(t.id varchar(10))) dbo.teams t t.id in ( select t2.id dbo.projects p inner join dbo.teamprojects tp on p.id = tp.idproject inner join dbo.teams t2 on t2.id = tp.idteam p.idparent = @projectid or p.id = @projectid ); end; return @list; end; go create function [dbo].[getprojectsforproject] ( @projecttype varchar(1) , --either 'a' ascendants or 'd' descendants @projectid int ) returns varchar(max) begin declare @projects varchar(max); if @projecttype = 'a' begin --get projects parents of @projectid select @projects = coalesce(@projects+','+cast(p.idparent varchar(10)) , cast(p.idparent varchar(10))) dbo.projects p p.id in ( select p.idparent dbo.projects p p.id = @projectid ); end; else begin --get projects children of @projectd select @projects = coalesce(@projects+','+cast(p.id varchar(10)) , cast(p.id varchar(10))) dbo.projects p p.idparent in ( select p.id dbo.projects p p.idparent = @projectid or p.id = @projectid ); end; return @projects; end; go using above project-based functions, query project-based query below.
select p.id projectid, dbo.getteamsforproject('a', p.id) ascendantteams, dbo.getteamsforproject('d', p.id) descendantteams, dbo.getprojectsforproject('a', p.id) ascendantprojects, dbo.getprojectsforproject('a', p.id) descendantprojects dbo.projects p; a screen shot of sample data used , final queries given below.
Comments
Post a Comment