April 3, 2009 at 1:05 pm
Hello, we have a problem at hand and we don't how to accomplish this with sqlserver.
We have one table lets callit users. And we need to know the follow up accounts for every user. the thing is that we might not have the same id for every account from a user but we know we can track a thread of created accounts. And all this is ONE table.
can we use functions for this (recursively)?
Or stored procedures?
Or with sql queries? We have tried this approach and is headache
Can you provides us with examples of how to with functions or SP greatly appreciated
April 3, 2009 at 1:12 pm
Can you provide the relevant table structure, some sample data, and a sample of what the output should look like? I'm having a little trouble visualizing what you're describing, and those will definitely help.
- 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
April 3, 2009 at 1:32 pm
The general is as follows
Table:FollowUp
ID
user
turnedTo
account
Policy
let's say that user 8 received a modify petition and this user changes a policy (1001) and turns the petition for further modification to users 10 and 3.
User 10 then changes policy #1101 and #234 and turns, for further modification to user 113 and 12
User 3 changes policies #4454 and #456 and #2345 and turns them to user id 16
i need the tree structure in a table but don't know how deep it will go at one point in time or how many policies a "starter user" will issue for further approvals.
April 3, 2009 at 1:36 pm
That's a pretty standard "adjacency hierarchy" and can be resolved in SQL 2005 by a recursive Common Table Expression. If you look up Common Table Expressions (CTEs) in Books Online, it has a good example of how to solve that kind of thing. If you take a look at it, and can't figure it out from there, come back here and ask questions, and we'll gladly help with answers.
- 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 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply