人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

Mysql5の表結合の質問です。
以下のような4つのテーブルがあったと仮定します。
person_tblが所属するカテゴリ(cat_tbl)だけをsqlで抽出したいのです。


テーブル名:person_tbl
pk per_id
フィールド名:per_id, per_name,
1, 山田
2, 田中
3, 佐藤


テーブル名:cat_tbl
pk cat_id
フィールド名:cat_id, cat_name
1, 九州
2, 関東
3, 近畿
4, 北陸


テーブル名:subcat_tbl
pk sub_id
フィールド名:sub_id, sub_name, cat_id
1, 沖縄, 1
2, 福岡, 1
3, 東京, 2
4, 千葉, 2
5, 京都, 3
6, 大阪, 3
7, 京都, 3
8, 福井, 4


person_tblとsubcat_tblのT字型テーブル
テーブル名:per_to_sub
pk per_id, sub_id
フィールド名:per_id, sub_id
1, 1
1, 2
2, 3
3, 4
3, 8


結果配列
1, 九州
2, 関東
4, 北陸


どのようなsqlを発行すればいいでしょうか?
少し複雑になると頭が混乱します。
そもそも、このような設計で希望の配列が抽出できますか?
よろしくお願いします。

●質問者: seadwell
●カテゴリ:ウェブ制作
✍キーワード:MySQL PK SQL カテゴリ フィールド
○ 状態 :終了
└ 回答数 : 4/4件

▽最新の回答へ

1 ● y-kawaz
●30ポイント

こんな感じでしょうか。

SELECT * FROM cat_tbl WHERE cat_id IN (
 SELECT cat_id FROM subcat_tbl WHERE sub_id IN (
 SELECT sub_id FROM per_to_sub WHERE per_id IN (
 SELECT per_id FROM person_tbl
 )
 )
);
◎質問者からの返答

こんなに、inって続けられるんですか。

知りませんでした。

回答をありがとうございます。


2 ● miyamuko
●25ポイント

検証していないのですが、こちらの SQL で希望の結果が得られると思います。

SELECT DICTINCT cat.*
FROM person_tbl per
 JOIN per_to_sub persub ON per.per_id = persub.per_id
 JOIN subcat_tbl subcat ON persub.sub_id = subcat.sub_id
 JOIN cat_tbl cat ON cat.cat_id = subcat.cat_id
◎質問者からの返答

希望の結果を得ることができました。

ありがとうございます。


3 ● HALSPECIAL
●25ポイント

こちらでいかがでしょうか?


SELECT DISTINCT cat_tbl.cat_id, cat_tbl.cat_name
FROM ((cat_tbl 
 INNER JOIN subcat_tbl 
 ON cat_tbl.cat_id = subcat_tbl.cat_id) 
 INNER JOIN per_to_sub 
 ON subcat_tbl.sub_id = per_to_sub.sub_id) 
 INNER JOIN person_tbl 
 ON per_to_sub.per_id = person_tbl.per_id;


◎質問者からの返答

こちらも希望どおりに動きました。

回答ありがとうございました。


4 ● kn1967
●10ポイント

person_tbl の各レコードに合わせて

他のテーブルからデータを引っ張ってくるのであれば下記のような感じですが

person_tbl に sub_id を含むべきですし

subcat_tbl に cat_name を含んでしまえば

テーブルは2つで済むのですから、後々楽になると思いますよ。


(1)using

SELECT T1.per_id, T4.cat_name
FROM person_tbl AS T1
LEFT JOIN per_to_sub AS T2 USING(per_id)
LEFT JOIN subcat_tbl AS T3 USING(sub_id)
LEFT JOIN cat_tbl AS T4 USING(cat_id);

(2)on

SELECT T1.per_id, T4.cat_name
FROM person_tbl AS T1
LEFT JOIN per_to_sub AS T2 ON T2.per_id = T1.per_id
LEFT JOIN subcat_tbl AS T3 ON T3.sub_id = T2.sub_id
LEFT JOIN cat_tbl AS T4 ON T4.cat_id = T3.cat_id;

(3)where

SELECT T1.per_id, T4.cat_name
FROM person_tbl AS T1
JOIN per_to_sub AS T2
JOIN subcat_tbl AS T3
JOIN cat_tbl AS T4
WHERE (T2.per_id = T1.per_id) AND (T3.sub_id = T2.sub_id) AND (T4.cat_id = T3.cat_id);

MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.2.7.1 JOIN 構文


データ量がすくなければ上記いずれも体感できるほどの差はないと思います。

データが増えてくれば3が高速、1が低速になる傾向があったりはしますが

それも環境によるので、EXPLAINで MySQLのオプティマイザが

どのような実行プランを出してくるかを判定してから適宜チョイスする必要性が出てきます。

MySQL :: MySQL 5.1 リファレンスマニュアル :: 6.2.1 EXPLAINを使用して、クエリを最適化する


とりあえずは、いろいろな結合方法があることを順に覚えていってください。

※いきなり書いているので動作確認はしていません。内容を確認しながら使ってください。

◎質問者からの返答

詳しい回答をありがとうございます。

> subcat_tbl に cat_name を含んでしまえば

そうですね。

cat_tblはデータ量が多くないのでそれでもいいと思います。

> person_tbl に sub_id を含むべきですし

person_tblのレコードは、subcat_tblの複数のレコードに所属します。

その場合、困ったことになりませんかね。

レコードの例が判りづらいですね。

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ