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

回答の条件
  • 1人2回まで
  • 登録:2009/06/10 20:05:45
  • 終了:2009/06/13 14:28:36

回答(4件)

id:y-kawaz No.1

y-kawaz回答回数1420ベストアンサー獲得回数2252009/06/10 21:31:50

ポイント30pt

こんな感じでしょうか。

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
    )
  )
);
id:seadwell

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

知りませんでした。

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

2009/06/10 21:36:20
id:miyamuko No.2

miyamuko回答回数29ベストアンサー獲得回数112009/06/10 22:35:34

ポイント25pt

検証していないのですが、こちらの 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
id:seadwell

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

ありがとうございます。

2009/06/11 00:35:41
id:HALSPECIAL No.3

HALSPECIAL回答回数407ベストアンサー獲得回数862009/06/10 22:16:30

ポイント25pt

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


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;


id:seadwell

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

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

2009/06/11 00:36:23
id:kn1967 No.4

kn1967回答回数2915ベストアンサー獲得回数3012009/06/10 22:43:11

ポイント10pt

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を使用して、クエリを最適化する


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

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

id:seadwell

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

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

そうですね。

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

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

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

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

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

2009/06/10 22:57:12
  • id:kn1967
    それぞれの出身地方を抜き出すのと勘違いしてました。
    出身地方だけを抜き出す場合は下記です。

    SELECT T1.per_id, T4.cat_name
         ↓
    DISTINCT SELECT T4.cat_id, T4.cat_name
        もしくは
    DISTINCT SELECT T4.*

  • id:kn1967
    >person_tblのレコードは、subcat_tblの複数のレコードに所属します。
    >その場合、困ったことになりませんかね。

    単純に出身地方のように考えていたので1対1だと思い込んでいました。
    1対nであれば都合3テーブルになるのは致し方ないかもしれません。

    非正規化になりますが1対nのnの最大数を決定できる場合は
    person_tbl : per_id, per_name, sub_id1, sub_id2, sub_id3
    などという手も無きにしも非ずですが、これはケースバイケースなので
    とりあえずは3テーブルが妥当かな。

  • id:seadwell
    コメントいただきありがとうございます。
    また、丁寧な回答をありがとうございます。

    例えが紛らわしいですね。
    次回から気をつけます。

    nの最大数は未定なので3テーブルで行きます。
    頑張っているのですが、表結合はなかなか上達しません。
    皆さん含む kn1967 さんがいろんな回答を示してくれるので非常に勉強になります。
    参考に頂いたリファレンスをじっくり読んでみます。
    ありがとうございました。
  • id:hijk04
    (はてなにより削除しました)

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません