February 20, 2004 at 11:16 pm
Hello all, I have done some digging here, but can't seem to find my answer. I'm fairly new to TSQL so I'm hoping something here can give me a hand. I have a table which has 4 columns:
id (int), parent (int), name (varchar), tier (int)
These items are linked by parent (each item must have 1 and only 1 parent), but the list can be infinitely long, with every child have 0 to many children. Currently the deepest relationship is 6 tiers but that will eventually grow. Root elements are items with parent= -1, tier 1.
I need to create a query to get all the id's whose parent is that root, and then get all of those children's children, and so on. I will also need to allow for multiple roots in this query. ie in pseudocode: "get all children, grandchildren, ... for roots (root1,root2)"
I could not understand this post / didn't think it was 100% relevant: ...forumid=8&messageid=12950
Any help is greatly appreciated. I currently have a giant UNION query which only accounts for 6 tiers, and is not returning results correctly.
February 23, 2004 at 7:53 am
Here's some more information, if this helps: I am searching for questions related to the current glossary term, for a registered student. The glossary terms are referenced via a foreign key (glossary table ID number) in the answers table, which has a foreign key referencing the question ID in the question table. The questions are classified into various categories, and the student subscribes to 1 or more root category. The search should only return questions which the student is currently subscribed to. Here's a bit of what I'm currently working with, if this helps...
SELECT DISTINCT Q_ID
FROM BS_Answers
WHERE (AnswerValue = @answerValue) AND (glossaryIDLink = @glsTermID)
AND (Q_ID IN
--tier 1
(SELECT BS_Q_ID
FROM BS_ClassificationValues
WHERE BS_ClassificationID IN
(SELECT ID
FROM [BS_Meta-Table]
WHERE id IN
(SELECT sub_id
FROM BS_StuSubscribers
WHERE Student_ID = @myID AND Date_Exp > GETDATE()) OR
id IN
--tier 2
(SELECT DISTINCT id
FROM [BS_Meta-Table]
WHERE (Tier = 2) AND Parent IN
(SELECT sub_id
FROM BS_StuSubscribers
WHERE Student_ID = @myID AND Date_Exp > GETDATE())) OR
id IN
--tier 3
etc.
February 23, 2004 at 8:24 am
Take a look at this implementation is very easy and self maintained. It has some minor defects but it will help you get where you need
* Noel
February 23, 2004 at 11:29 am
noeld,
thanks! I'll take a look at this and see if I can use it.
Brett
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply