-- Collection of code snippets by Arne Vajhøj 
-- posted to eksperten.dk, usenet and other places (2002-now) 
CREATE TABLE stuff (id INTEGER PRIMARY KEY,name VARCHAR(50),parent INTEGER)
GO

INSERT INTO stuff VALUES(1,'tools',0)
GO
INSERT INTO stuff VALUES(2,'hammer',1)
GO
INSERT INTO stuff VALUES(3,'screwdriver',1)
GO
INSERT INTO stuff VALUES(4,'fruits',0)
GO
INSERT INTO stuff VALUES(5,'yellow fruits',4)
GO
INSERT INTO stuff VALUES(6,'green fruits',4)
GO
INSERT INTO stuff VALUES(7,'apple',6)
GO
INSERT INTO stuff VALUES(8,'banana',5)
GO
INSERT INTO stuff VALUES(9,'grape',6)
GO

WITH x (id, name, parent) AS
(
    SELECT id,name,parent FROM stuff WHERE name = 'fruits'
    UNION ALL
    SELECT stuff.id,stuff.name,stuff.parent FROM stuff INNER JOIN x ON stuff.parent = x.id
)
SELECT * FROM x
GO

DROP TABLE stuff
GO
