@Alex Martelli's answer is great!
But it work only for one element at time (WHERE name = 'Joan')
If you take out the WHERE clause, the query will return all the root rows together...
I changed a little bit for my situation, so it can show the entire tree for a table.
table definition:
CREATE TABLE [dbo].[mar_categories] (
[category] int IDENTITY(1,1) NOT NULL,
[name] varchar(50) NOT NULL,
[level] int NOT NULL,
[action] int NOT NULL,
[parent] int NULL,
CONSTRAINT [XPK_mar_categories] PRIMARY KEY([category])
)
(level is literally the level of a category 0: root, 1: first level after root, ...)
and the query:
WITH n(category, name, level, parent, concatenador) AS
(
SELECT category, name, level, parent, '('+CONVERT(VARCHAR (MAX), category)+' - '+CONVERT(VARCHAR (MAX), level)+')' as concatenador
FROM mar_categories
WHERE parent is null
UNION ALL
SELECT m.category, m.name, m.level, m.parent, n.concatenador+' * ('+CONVERT (VARCHAR (MAX), case when ISNULL(m.parent, 0) = 0 then 0 else m.category END)+' - '+CONVERT(VARCHAR (MAX), m.level)+')' as concatenador
FROM mar_categories as m, n
WHERE n.category = m.parent
)
SELECT distinct * FROM n ORDER BY concatenador asc
(You don't need to concatenate the level field, I did just to make more readable)
the answer for this query should be something like:
I hope it helps someone!
now, I'm wondering how to do this on MySQL... ^^