help with recursive query

Question

Post reply

help with recursive query

sisernhagen

Newbie

Points: 2
More actions
December 4, 2008 at 12:12 pm

#77147

I have a table similar to the one below with hierarchical data. I need to create a query that displays every account and the folder its contained in. Accounts can be inside an infinite number of groups but they can only have one folder. Also, all folders have a parent_id of 0 meaning that they all are direct children of root. Some accounts are not in a folder in which case their folder will be displayed as NULL. I've tried to figure this out many times but I cant seem to do it. Any help would be greatly appreciated. Thanks.
id type name parent_id
0root root null
1accountFred Jones 10
2accountLisa Jones 10
3accountLarry Jones 11
4accountBarry Jones 11
10group Jones Adults 12
11group Jones Children 12
12folder California Familes 0
13 account Bachelor Dave 0
14 account Bachelor Jim 0

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 1

Consider this

DECLARE@Sample TABLE

(

id INT,

type VARCHAR(20),

name VARCHAR(20),

parent_id INT

)

INSERT@Sample

SELECT 0, 'root', 'root', nullUNION ALL

SELECT 1, 'account', 'Fred Jones', 10 UNION ALL

SELECT 2, 'account', 'Lisa Jones', 10 UNION ALL

SELECT 3, 'account', 'Larry Jones', 11 UNION ALL

SELECT 4, 'account', 'Barry Jones', 11 UNION ALL

SELECT10, 'group', 'Jones Adults', 12 UNION ALL

SELECT11, 'group', 'Jones Children', 12 UNION ALL

SELECT12, 'folder', 'California Familes', 0 UNION ALL

SELECT13, 'account', 'Bachelor Dave', 0 UNION ALL

SELECT14, 'account', 'Bachelor Jim', 0

;WITH Yak (id, parent_id, pathid)

AS (

SELECTTOP 100 PERCENT

id,

parent_id,

cast(name as varchar(max))

FROM@Sample

WHEREParent_ID IS NULL

ORDER BYname

UNION ALL

SELECTTOP 100 PERCENT

s.id,

s.parent_id,

y.pathid + '>' + s.name

FROM@Sample AS s

INNER JOINYak AS y ON y.id = s.parent_id

)

SELECT*

FROMYak

order bypathid

idparent_idpathid

0NULLroot

130root>Bachelor Dave

140root>Bachelor Jim

120root>California Familes

1012root>California Familes>Jones Adults

110root>California Familes>Jones Adults>Fred Jones

210root>California Familes>Jones Adults>Lisa Jones

1112root>California Familes>Jones Children

411root>California Familes>Jones Children>Barry Jones

311root>California Familes>Jones Children>Larry Jones

N 56°04'39.16"
E 12°55'05.25"

Garadin One Orange Chip Points: 29613 More actions · Answer 2

sisernhagen,

Note that Peso's solution won't work if you're using SQL 2000.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

sisernhagen Newbie Points: 2 More actions · Answer 3

Thank you both! I'm only using sql server 2005 so we are on the right track. I need to join to this query as part of a bigger query. And need something like what is shown below. I went through the query above to try to get it to do what I need but I still cant figure it out. Maybe it needs to recurse more times so that the CTE contains every account with all of its parents. You could then filter out the list so only ones that have a parent type = folder are returned. I'm not sure how to do this unfortunately.

account_id account_name folder_id folder_name

1 Fred Jones 12 California Families

2 Lisa Jones 12 California Families

3 Larry Jones 12 California Families

4 Barry Jones 12 California Families

13 Bachelor Dave NULL NULL

14 Bachelor Jim NULL NULL