Complex Query

Hi,
I have a tree database with the following fields:
- themeid
- description
- parent id
I have the following registries:
Themeid description parentid
1 food null
2 cars null
3 others null
4 meat 1
But i need to generate a list like:
Food + (level or depth, in this case 1)
Meat + (level or depth in this case 2)
Cars + (level or depth, in this case 1)
Others + (level or depth, in this case 1)
Meat + (level or depth, in this case 1)
Im trying to figure out how to use the with clause to get the list with the
level or depth or each registry, but at this moment i dont have idea about
how to do it.
Any help would be appreciated.
Thanks a lot.
Kind regards.This question was asked and answered in the programming group. Please don't
post the same question in multiple groups as this can cause duplication of
effort.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Josema" <Josema@.discussions.microsoft.com> wrote in message
news:4A9CF7D0-E8BA-4054-9F0C-53F521D0EC9C@.microsoft.com...
> Hi,
> I have a tree database with the following fields:
> - themeid
> - description
> - parent id
> I have the following registries:
> Themeid description parentid
> 1 food null
> 2 cars null
> 3 others null
> 4 meat 1
> But i need to generate a list like:
> Food + (level or depth, in this case 1)
> Meat + (level or depth in this case 2)
> Cars + (level or depth, in this case 1)
> Others + (level or depth, in this case 1)
> Meat + (level or depth, in this case 1)
> Im trying to figure out how to use the with clause to get the list with
> the
> level or depth or each registry, but at this moment i dont have idea about
> how to do it.
> Any help would be appreciated.
> Thanks a lot.
> Kind regards.
>|||Here is one way using recursive CTE in SQL Server 2005:
CREATE TABLE Themes (
themeid INT PRIMARY KEY,
description VARCHAR(35),
parentid INT REFERENCES Themes(themeid));
INSERT INTO Themes VALUES (1, 'food', NULL);
INSERT INTO Themes VALUES (2, 'cars', NULL);
INSERT INTO Themes VALUES (3, 'others', NULL);
INSERT INTO Themes VALUES (4, 'meat', 1);
WITH ThemesPath
AS
(SELECT themeid, description, parentid,
CAST('.' + CAST(themeid AS VARCHAR(8)) + '.'
AS VARCHAR(2000)) AS theme_path,
1 AS depth
FROM Themes
WHERE parentid IS NULL
UNION ALL
SELECT T.themeid, T.description, T.parentid,
CAST(P.theme_path + CAST(T.themeid AS VARCHAR(8)) + '.'
AS VARCHAR(2000)),
P.depth + 1
FROM Themes AS T
JOIN ThemesPath AS P
ON T.parentid = P.themeid)
SELECT themeid, description, parentid, depth
FROM ThemesPath
ORDER BY theme_path;
HTH,
Plamen Ratchev
http://www.SQLStudio.com