Robert Zajda wrote:
I have a table
CREATE TABLE acategories {
id integer not null primary key
category string
parent_category integer references acategories.id
)
query:
SELECT
a.id AS 'id',
a.category AS 'Category',
b.category AS 'Parent category'
FROM acategories AS a
LEFT JOIN acategories AS b ON a.parent_category=b.id
everything works ok, but i need to add new field with deepness level
of each row.
Is it possible ?
Robert,
I posted a sample database tree technique to this list some time ago
(see http://www.mail-archive.com/sqlite-users@sqlite.org/msg13225.html
). It shows how to get all the nodes on the path from the root to a
particular tree node. You can simply count these results to get your
"deepness level" (I would suggest calling it the depth instead :-)).
You can join your tree with these depths and use the depths to inserts
spaces to produce an indented report from the tree.
Alternatively, you could create a user defined function that simply
counts the number of / separator characters in the path string to
determine the depth of a node. This would almost certainly be faster and
easier to use.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------