April 2, 2009 at 9:25 am
I have a table where i want to trace a thread by using self join.
ID (int), empid(int), forwardeddescription(varchar), forwardedid(int)
1 1 NULL NULL
2 2 first forward 1
3 2 NULL NULL
4 5 second forward 2
5 9 third forward 4
here forwardedcomplainid is the ID column which is saved when the thread is passed on to another employee.
i want to get all the forwarddescription for the thread. We can trace a thread by forwardedid.
i want to make a stored procedure for this without using a cursor.
basically when i make an input of id=5 i should get all the forwardeddescription for this thread.
and it should not include id=3 (because it does not belong to the thread)
April 2, 2009 at 9:28 am
That's a standard "adjacency hierarchy". You can resolve it with a recursive CTE. Look up Common Table Expressions in Books Online, and it will have an example of that kind of thing. Applies directly to what you're doing.
If, after reading the part in BOL, you still need help on this, feel free to ask. But you'll probably get what you need right there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply