December 15, 2004 at 9:38 am
Hi all,
I have been working on this for past 2 days but can't figure out how to do it. I would be really very thankful if anyone could help me with this.
I have 2 tables:
Topics
TopicId ParentID
1 null
2 null
3 1
4 1
5 2
6 3
7 null
8 7
9 6
MsgID TopicID
1 4
2 5
3 8
4 9
5 4
First of all, I have to get all the topics and sub-topics that belong to topiid=1. So basically the result set would have topicids 1,3,4,6,9.
Next I need to grab the messages for that topic. The message is attached only to topics that don't have any further sub-topics. So for Topic 1, I would need to get all msgs that come in any of its sub-topics like msgids 1,4,5 in this example.
I hope its not too ambiguous. Any help will be greatly appreciated. Thanks in advance.
-Goldy
December 15, 2004 at 10:01 am
It would be useful if you could display the tables that you expect to see as the output to avoid any confusion.
December 15, 2004 at 10:28 am
Thanks Jonathan. The result set I am expecting would look like this for the 1st query:
select topicid from topics where topicid = 1 or (any topic that has a parentid of 1 or any of the sub-topics of 1)
TopicId
1
3
4
6
9
select msgid from msgs where topicid = 1 or (any topic that has a parentid of 1 or any of the sub-topics of 1)
MsgID
1
4
5
does this help?
December 15, 2004 at 11:49 am
To acheive this using only the data you've presented here - you will need 2 stored procedures the first is very simple...
CREATE PROCEDURE dbo.GetHierarchy
@Root int
AS
SET NOCOUNT ON
CREATE TABLE #MyTempTable (TopicID int)
exec GetTopics @Root
SELECT * FROM #MyTempTable
DROP TABLE #MyTempTable
GO
As you can see - all it does is create a temporary table to hold the selected values and then calls a second stored procedure to populate it. Since you have no way of knowing how many levels deep your hierarchy goes, this second stored proc must call itself recursively...
CREATE PROC dbo.GetTopics
(
@Root int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @TopicID int
INSERT INTO #MyTempTable(TopicID) Values (@Root)
SET @TopicID = (SELECT MIN(TopicID) FROM Topics WHERE ParentID = @Root)
WHILE @TopicID IS NOT NULL
BEGIN
EXEC dbo.GetTopics @TopicID
SET @TopicID = (SELECT MIN(TopicID) FROM Topics WHERE ParentID = @Root AND TopicID > @TopicID)
END
END
GO
The catch with this solution is that SQL will only allow a stored procedure to nest itself 32 times. If you have trees in your hierarchy that exceed this limit, you will get the error...Maximum stored procedure nesting level exceeded (limit 32).
Although the above solution will give you the desired result, a more elegant solution would be to include hierarchical data in you main table. As your application matures, you are probably going to be faced with other situations which will require access to the data based on the hierarchy. Instead of creating a myriad of complicated stored procedures, I would strongly consider reading the article "Maintaining Hierarchies" by Itzik Ben-Gan http://www.windowsitpro.com/SQLServer/Article/ArticleID/8826/8826.html
This article explains how to set up a "key-path" hierarchy which can be done in about 15 minutes. I've used it many times with great success.
Good Luck!
December 15, 2004 at 12:28 pm
Thanks a lot, Earl. Interesting solution. I briefly looked at the article.. that seems to be exactly what I was looking for. Looks like it should work fine for me...will let u know, if I run into a problem. Thanx again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply