カテゴリに紐づいているサブカテゴリを取得するには

質問

SQLで質問があります。

カテゴリテーブルがあります。
カテゴリテーブルのフィールドは以下の通りです。

　・カテゴリコード
　・カテゴリ名
　・親コード

例えば下記のようなデータとします。
1000｜洋服　　｜NULL
1100｜子供服　｜1000
1110｜ズボン　｜1100
1111｜半ズボン｜1110
1112｜長ズボン｜1110
1120｜シャツ　｜1100
1121｜Ｔシャツ｜1120
1200｜婦人服　｜1000
1300｜男性服　｜1000
　　　　　・
　　　　　・
　　　　　・
　　　　　・

カテゴリはプルダウンで

洋服(1000)
　　　　　→子供服(1100)
　　　　　　　　　　→子供服(1100)
　　　　　　　　　　　　　　　→長ズボン(1111)

の順に選択しました。

「カテゴリ」のプルダウンを選択すると選択したカテゴリに紐づいている「サブカテゴリ」のプルダウンを順々に表示しています。
データベースに登録されているカテゴリコードは最後に選択した「長ズボン」の「1111」です。

この場合、SQLで「長ズボン」の「1111」をキーにして「洋服(1000)」「子供服(1100)」「子供服(1100)」というカテゴリを取得するにはどのように記述したらいいのでしょうか。

説明、下手ですみませんが理解していただけましたでしょうか。
申し訳ありませんがアドバイス、宜しくお願いします。

あ、データベースはpostgreSQL8.4.9です。
では、失礼します。

回答No.3

Siegrune

1111の親を全部探すというのは、再帰SQLを使えばつくるのは簡単にできるのですが・・・。

http://lets.postgresql.jp/documents/technical/with_recursive
の
再帰SQL (WITH RECURSIVE)
にあるサンプルの
tree.parent = r.id
を
tree.child = r.id
にするという話。

ただし、登録ミスした内容によっては無限ループして異常終了するとか
別なところで問題はあります。

テーブル設計については、親子で表現するのではなく、
大分類コード
中分類コード
小分類コード
・・・
とそれぞれ持たせるほうがシンプルでわかりやすいとは思いますが。
（実際に子供服の売上を集計したいときとかに不自由なコード系になってしまいます。
　・・・値の桁位置が大分類/中分類/・・・と意味を持っているなら別ですが、
　0～9で足りなくなってABC・・・と増えていくのは考え物ですし。）

お礼

ストアドプロシージャでいい感じまできたのでストアドプロシージャで取得する方向で進めたいと思います。
皆さん、貴重な意見、またアドバイス、本当にありがとうございました。

補足

返答が遅くなってすみません。
アドバイス、ありがとうございます。
なぜこのようにしたかというと大分類、中分類、小分類以上にカテゴリが細かく設定できるようにしているみたいです。
なので仕様というか、テーブル構造は変更できません。
貴重なご意見、本当にありがとうございます。

回答No.2

wipe3out

すみません、上で回答したものです。

回答に紛らわしい部分があったので訂正です。
参考ＵＲＬで紹介した方法ですが、質問者様のテーブル構造だとだめっぽいです。
前に見た覚えがあったので紹介したのですが、ちゃんと確認してませんでした。。。

多分、以下のようなテーブル構造を前提とした記事になっています。

カテゴリ名、コード最小値、コード最大値
洋服、1000、1399
子供服、1100、1199
ズボン、1110、1119
半ズボン、1111、1111
（略）
婦人服、1200、1299

上記の構造だと、子供服以下のノードを列挙したい場合、「子供服行のコード最小値以上、コード最大値以下」という条件でＳＥＬＥＣＴすれば、子ノードがずらっと取れます。

質問のように逆をやりたい場合、半ズボンのコードが「コード最大値」、「コード最小値」の間に含まれるという条件でＳＥＬＥＣＴすれば、やはり親ノードがずらっと取れます。適当ですが以下に参考のコードを書きます。

SELECT * FROM TABLE WHERE コード最小値 <= 1111 AND コード最大値 >= 1111

テーブル設計が変更可能なら、参考ＵＲＬを確認して、上記のような構造にするほうが後々何かと楽かとおもいますよ～

お礼

ストアドプロシージャでいい感じまできたのでストアドプロシージャで取得する方向で進めたいと思います。
皆さん、貴重な意見、またアドバイス、本当にありがとうございました。

補足

返事が遅くなってすみません。

下記の「子供服(1100)」「子供服(1100)」の箇所は誤りです。大変、申し訳ありませんでした。

> ただ、質問文だと「洋服(1000)」「子供服(1100)」「子供服(1100)」のように「子供服」を重複して抽出しているようなのですが、これは誤記でしょうか？？それとも何か意味があるのでしょうか？この辺、重要なら詳しく書いたほうがいいかもです。

一応、試行錯誤してストアドプロシージャでカテゴリコードをキーにして親コードを取得。親コードがNULLになるまで繰り返してSQL文を生成して何とか取得する事ができましたが・・・

回答No.1

wipe3out

ＳＱＬに関しては初心者なので、的外れかもしれませんが、解法を思いついたので投稿します。

1．コードで探す。

上記のサンプルが実物と違うかもしれないので、なんともいえないのですが、コードを使って絞ればよいのではないでしょうか？？上記のコード体系の性質が実物と同じなら、１１１１というコードを末尾から0に置き換えて行けば親カテゴリのコードである1000、1100、1110が取得できるので、後は単純にＳＥＬＥＣＴを使って抽出すれば良い気がします。コードは（適当ですが）以下のような感じです。

SELECT * FROM TABLE WHERE code = IN(1000, 1100, 1110)

たとえば、「ネクタイ（1310）」というのがあれば、末尾が0なので、これは三番目の階層ですよね。なので、三桁目から順に0に置き換えたコード1000、1300が親カテゴリのコードになります。これでＳＥＬＥＣＴすればいい気がします。

ただ、質問文だと「洋服(1000)」「子供服(1100)」「子供服(1100)」のように「子供服」を重複して抽出しているようなのですが、これは誤記でしょうか？？それとも何か意味があるのでしょうか？この辺、重要なら詳しく書いたほうがいいかもです。

また、コードのトリックが使えない場合に関しては、参考ＵＲＬの「2-7．パスを列挙する（列持ちバージョン）」、「2-6．部分木を列挙する」を応用すれば出来ると思います。参考サイトはとても勉強になるので、暇なときに一通り読むと幸せになれるかもしれません。

がんばってください！

参考URL：

http://www.geocities.jp/mickindex/database/db_tree_ns.html#LocalLink-intro