Complex Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Josema - 25 Mar 2008 11:50 GMT

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.

Reply to this Message

Dan Guzman - 25 Mar 2008 14:01 GMT

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.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> Hi,
>
[quoted text clipped - 28 lines]
> Thanks a lot.
> Kind regards.

Reply to this Message

Plamen Ratchev - 25 Mar 2008 14:14 GMT

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

Reply to this Message