-- Collection of code snippets by Arne Vajhøj 
-- (likely posted to comp.os.vms/INFO-VAX, INFO-TPU, MACRO32, eksperten.dk, newz.dk, LinkedIn or other place sometime between 1990 and now) 
CREATE TABLE tt (
    id INTEGER NOT NULL,
    parent INTEGER,
    part VARCHAR(32),
    PRIMARY KEY(id)
);
INSERT INTO tt VALUES(1, NULL, 'movies');
INSERT INTO tt VALUES(2, 1, 'dvd');
INSERT INTO tt VALUES(3, 2, 'horror');
INSERT INTO tt VALUES(4, 2, 'action');

SELECT t1.part,t2.part,t3.part,t4.part
FROM tt t1 LEFT JOIN tt t2 ON t1.parent=t2.id
           LEFT JOIN tt t3 ON t2.parent=t3.id
           LEFT JOIN tt t4 ON t3.parent=t4.id
WHERE t1.id = 3;

SELECT t1.part,t2.part,t3.part,t4.part
FROM tt t1 LEFT JOIN tt t2 ON t1.parent=t2.id
           LEFT JOIN tt t3 ON t2.parent=t3.id
           LEFT JOIN tt t4 ON t3.parent=t4.id
WHERE t1.id = 4;

DROP TABLE tt;
