请教一个join联接查询的问题

表treetest结构如下：

SQL code

catid    name            parentid

1    electronics        (null)
2    portable electronics    1
3    television        1
4    tube            3
5    lcd            3
6    plasma            3
7    mp3            2
8    cd            2
9    2way radios        2
10    flash            7

欲得到如下查询效果：

SQL code

name1        name2            name3        name4

electronics    portable electronics    mp3        flash
electronics    portable electronics    cd
electronics    portable electronics    2way radios
electronics    television        tube
electronics    television        lcd
electronics    television        plasma
electronics    television        plasma

我查了一些资料之后编写了如下这样一条长长的语句可以实现以上效果，但总是觉得怪怪的，好像有点冗长。

SQL code

SELECT a.name AS name1, b.name AS name2, c.name AS name3, d.name AS name4
FROM treetest AS a
LEFT JOIN treetest AS b ON a.catid=b.parentid
LEFT JOIN treetest AS c ON b.catid=c.parentid
LEFT JOIN treetest AS d ON c.catid=d.parentid
WHERE a.name='electronics';

请教一下各位前辈，还有没有别的方法也可以实现这样的查询效果？或者如何优化上边的查询语句？

用递归调用SP来完成，因为你不知道有多少层

代码递归比较好