seadwell回答ポイント 90ptウォッチ

Mysql５の表結合の質問です。

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

• はてなアカウントでログイン
• 無料ユーザー登録

みんなの回答

• y-kawaz2009/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
      )
    )
  );
  

  スター

  • seadwell 2009/06/10 21:36:20

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

    知りませんでした。

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

miyamuko2009/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

スター

• seadwell 2009/06/11 00:35:41

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

  ありがとうございます。

HALSPECIAL2009/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;

スター

• seadwell 2009/06/11 00:36:23

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

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

kn19672009/06/10 22:43:11

満足10pt

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

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

person_tbl に sub_id を含むべきですし

subcat_tbl に cat_name を含んでしまえば

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




（１）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);




（２）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;




（３）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 構文




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

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

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

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

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




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

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

スター

• seadwell 2009/06/10 22:57:12

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

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

  そうですね。

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

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

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

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

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

• その他の回答を読む

この質問へのコメント

• kn19672009/06/10 22:53:46
  それぞれの出身地方を抜き出すのと勘違いしてました。
  出身地方だけを抜き出す場合は下記です。
  
  SELECT T1.per_id, T4.cat_name
  　　　　　↓
  DISTINCT SELECT T4.cat_id, T4.cat_name
  　　　　もしくは
  DISTINCT SELECT T4.*
  
  
• kn19672009/06/11 00:30:38
  ＞person_tblのレコードは、subcat_tblの複数のレコードに所属します。
  ＞その場合、困ったことになりませんかね。
  
  単純に出身地方のように考えていたので１対１だと思い込んでいました。
  １対ｎであれば都合３テーブルになるのは致し方ないかもしれません。
  
  非正規化になりますが１対ｎのｎの最大数を決定できる場合は
  person_tbl ： per_id, per_name, sub_id1, sub_id2, sub_id3
  などという手も無きにしも非ずですが、これはケースバイケースなので
  とりあえずは３テーブルが妥当かな。
  
  
• seadwell2009/06/11 00:41:58
  コメントいただきありがとうございます。
  また、丁寧な回答をありがとうございます。
  
  例えが紛らわしいですね。
  次回から気をつけます。
  
  ｎの最大数は未定なので３テーブルで行きます。
  頑張っているのですが、表結合はなかなか上達しません。
  皆さん含む kn1967 さんがいろんな回答を示してくれるので非常に勉強になります。
  参考に頂いたリファレンスをじっくり読んでみます。
  ありがとうございました。
• hijk042009/06/11 18:54:32
  （はてなにより削除しました）
  

質問の情報

登録日時

2009-06-10 20:05:45

終了日時

2009-06-13 14:28:36

回答条件

1人2回まで

この質問のカテゴリ

• ウェブ制作

この質問に含まれるキーワード

人気の質問

• 私は、【チルスポ】　という名前で、 チルスポット情報を共有するアプリを開発しています。 皆さんの知って…匿名質問者11日前
• 同じような体験をしたことがある人はいますか？。 ある日、お葬式の職業に就きたいなあなんて話題にしてい…匿名質問者13日前
• b型就労支援施設に通っている方に質問です。どういうことが通っていて辛いと感じますか？他の利用者の方と…匿名質問者18時間前
• 令和は読み方で言うなら霊和とも読めますよね？令和は縁起が良いでしょうか？。 霊和となって幽霊は大喜び…匿名質問者1日前
• 私の彼氏は毒親育ちで、うつ病を患い、家を追い出された後ホームレスになって、現在は何故か逮捕されて留置…匿名質問者23日前

メニュー

• 質問一覧
• カテゴリ一覧
• 無料ユーザー登録
• 人力検索はてなトップ

PC版

• お問い合わせ
• ヘルプ
• お知らせ
• はてなトップ