Looping to create joins 2

[Craig0201]

Hi,

Very stumped so all help appreciated....

I've got a table containing a hierarchy and a second table with the date in. In the second table, there is a self-join so that a record relates to a parent record in the same table. The number of parents relates to the number or records in the hierarchy table hence so does the number of joins I need. Can I do that programatically?

Craig

 

[tlbroadbent]

Check out the following links.

Expanding Hierarchies (SQL BOL)

http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_14_5yk3.asp

Using T-SQL to Generate a Resultset in Tree Form

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=19663

Trees in SQL (Joe Celko)

http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci537290,00.html

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[SemperFiDownUnda]

/me jaw drops.
It amazes me almost every single time....not only the amount of information you know but the amount of useful links you have Terry.

/me sticks a star on Terry's forehead.

 

[Craig0201]

Terry,

Thanks.

Although it didn't do the job I needed, there was enough there to help me write the procedure. My first real procedure with variables and things!

I'm in love.....*smile*

CREATE PROCEDURE GL_Hierarchy AS

BEGIN
--Get count of hierarchy levels--
SELECT GL_Code_Hierarchy_ID FROM GL_Code_Hierarchy_T
DECLARE @HierarchyCount INT
SELECT @HierarchyCount = @@rowcount
DECLARE @MaxHierarchyCount INT
SET @MaxHierarchyCount = @HierarchyCount
--Set up SQL to get combinations--
DECLARE @SQLSelect nvarchar(4000)
DECLARE @SQLJoin nvarchar(4000)
SET @SQLSelect = 'SELECT '

WHILE @HierarchyCount>0 BEGIN
BEGIN
IF @HierarchyCount = 1
SET @SQLSelect = @SQLSelect + CHAR(@HierarchyCount+64) + '.GL_Code_Hierarchy_Value_ID'
ELSE
SET @SQLSelect = @SQLSelect + CHAR(@HierarchyCount+64) + '.GL_Code_Hierarchy_Value_ID, '
END
BEGIN
IF @HierarchyCount = @MaxHierarchyCount
SET @SQLJoin = ' FROM GL_Code_Hierarchy_Value_T AS '+ CHAR(@HierarchyCount+64)
ELSE
SET @SQLJoin = @SQLJoin + ' INNER JOIN GL_Code_Hierarchy_Value_T AS ' + CHAR(@HierarchyCount+64) + ' ON ' + CHAR(@HierarchyCount+64) + '.GL_Code_Hierarchy_Value_ID = ' + CHAR(@HierarchyCount+65) + '.Parent_GL_Code_Hierarchy_Value_ID'
END
SET @HierarchyCount = @HierarchyCount-1
END
--Set full SQL string--
DECLARE @SQL nvarchar(4000)
SET @SQL = @SQLSelect + @SQLJoin
EXECUTE sp_executesql @SQL
END

GO

Craig