recursive query? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

recursive query?

Author

Topic

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2009-09-29 : 07:44:25

I have a table that has the following columns

organizationid
organization_parent
organization_name

I want to loop through all of the items in there and bring back the parent child relationship like

great grand parent / grand parent / parent / child

I dont know how many levels deep it will go, but as long as organization_parent isnt null, then keep looping until you have them all.

Is this do able?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 07:47:08

make use of recursive cte method

http://www.mssqltips.com/tip.asp?tip=1520

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 07:50:52

for your case, it will be something like:-


;With Org_CTE(ID,Name,Path)
AS
(
SELECT organizationid,organization_name,organization_name
FROM Table
WHERE organization_parent IS NULL
UNION ALL
SELECT t.organizationid,t.organization_name,c.Path + '/' + t.organization_name
FROM Table t
INNER JOIN Org_CTE c
ON c.organizationid=t.organization_parent
)

SELECT * from Org_CTE

OPTION (MAXRECURSION 0)

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2009-09-29 : 08:32:33

I tried this and its just not working, it is telling me that the "types dont match between then ancor and the recursive part in the column organization_sname of recursive query org_cte

with org_cte
as
select

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2009-09-29 : 08:35:53

I keep getting an error

types dont atch between the ancor and the recursive part in the column organization_sname of recursive query org cte

With Org_CTE
AS
(
SELECT
organization_id
, organization_name
FROM
organizations
WHERE
organization_parent IS NULL
UNION ALL
SELECT
t.organizationid
, t.organization_name + '/' + t.organization_name
FROM
organizations t
INNER JOIN Org_CTE c ON c.organization_id = t.organization_parent
)

SELECT * from Org_CTE

OPTION (MAXRECURSION 0)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 08:49:39

please try to run query as i gave you...you're missing a column in first select

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2009-09-29 : 09:53:36

this gives me "invalid column name 'organization_id'"

With Org_CTE(id, name, path)
AS
(
SELECT
organization_id
, organization_sname
, organization_sname
FROM
organizations
WHERE
organization_parent IS NULL
UNION ALL
SELECT
t.organizationid
, t.organization_sname
, c.path + '/' + t.organization_name
FROM
organizations t
INNER JOIN Org_CTE c ON c.organization_id = t.organization_parent
)

SELECT * from Org_CTE

OPTION (MAXRECURSION 0)

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2009-09-29 : 10:20:00

i changed the join to

inner join org_cte c on c.id = t.orgaization_parent

and now I am back to

Types don't match between the anchor and the recursive part incolumn "path" of the query "org_cte"

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2009-09-29 : 10:41:50

I fixed it, thanks for the help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 13:01:55

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources