階層構造データの効率的な検索

質問

ある製品の部品、その部品を構成する部品、そしてその……というデータをDBに格納しています。
　それぞれの部品にはIDがついていて、ツリーで見るとこんなかんじです。

100
+120
　+300
　　+250　
　　+290

　これは、100というIDを持った部品にはIDが120の部品があり、120の部品には300という部品があり、、、、ということを表しています。

　これを以下のようなテーブルに持っています。

items
id | child_id |
----------
100 | 120 |
120 | 300 |
300 | 250 |
300 | 290 |

　ここで、ある部品のIDをキーにして、その部品を構成する部品全て（つまり、子部品、孫部品、ひ孫部品……）のIDを検索するようなSQLを書きたいと思っていますが、巧い方法が思い浮かびません。（今は10階層あったら、10回SQLを発行しています）
　これを1回のSQLで、全てを検索することはできないでしょうか。言うまでもありませんが、10回のSQLよりもパフォーマンスは良くないと困りますが。。。
　周りの人はみんなできないんじゃないかと言っていますが、もしできる、という方法をご存じの方はご教示いただければと思います。
　DBはPostgreSQL8.2ですが、MySQL5を使っているアプリでも同様の問題があるので、そちらでも使えればうれしいです。
　よろしくお願いいたします。

回答No.1

chukenkenkou · ベストアンサー

PostgreSQLでは、Oracleに似た「階層問い合わせ」という機能が、標準搭載ではないですが、実装されています。

http://www.thinkit.co.jp/free/marugoto/2/1/23/1.html

MySQLでは、まだこの辺の機能は実装されていません。階層を制限した形で実装するとか、ストアドプロシジャで作りこむといった対処になると思います。

標準SQLでは、SQL99で「再帰クエリ」(with句を使用）が規定されており、MySQLでも将来的には実装するのではないかと思います。

お礼

ありがとうございます。
まったく予想外のご回答だったのでちょっと驚いてしまいました。そんなのがあるのですね。
しかし、このような機能が追加されなければならない、ということはやはり簡単には、少なくともSQL一発で実現するのは難しいか無理、ということでしょうか。

現在はJavaのメソッドを再帰で呼んで実現しているのですが、これだと「在庫が50以下」というような条件があったときでも、それを素直にwhereに書くと、親部品の在庫が50以上あると引っかからず、その部品の子どもに検索にいかなくなってしまうので、条件がつけられないんですよね。。。
とても勉強になりました。ありがとうございました。

補足

お礼に書き忘れましたが、顧客に尋ねたところ、標準搭載ではない機能を使うのはサポートの点で難しい、とのことでした。
一応補足です。