質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.52%

複数テーブルを外部キーで結合する方法

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 3,768

zico_teratail

score 467

初歩的な質問ですみません。
MySQLで3つ以上のテーブルを外部キーでどんどん結合していく方法について教えてください。

二つまでならJOINすることが出来たのですが、二つめのテーブル内にあるキーを元にさらに連結させて取り出すことが出来ません。

具体的には下記のようなテーブルと得たい結果があるとします。

◆member
------------
id | name | ability
------------
1 | tanaka | 4 
2 | suzuki | 3
3 | satou | 2
4 | honda | 4
------------

◆ability
------------
id | spec1 | spec2 | ...(略) | spec10
------------
1 | 4 | 1 | ...(略) | 50
2 | 2 | 8 | ...(略) | 25
3 | 18 | 3 | ...(略) | 10
4 | 1 | 7 | ...(略) | 9
------------


◆spec
------------
id | spec
------------
1 | PHP
2 | MySQL
3 | Perl
4 | Ruby
(略)
50 | C++
------------

得たい結果は、
------------
id | spec1 | spec2 | ...(略) | spec10
------------
tanaka PHP Ruby (略) C++

・・・という感じです。


このテーブルに対して以下のようなクエリでいけるかと思ったけどダメでした。

SELECT * 
FROM  `member` AS a
LEFT JOIN  `ability` AS b ON b.`id` = a.`ability_id`
LEFT JOIN  `spec` AS c ON c.`id` = b.`spec1` AND c.`id` = b.`spec2` (中略) AND c.`id` = b.`spec10`
WHERE  `id` = 1


どうダメだったのかというと、以下のようにspec部分がNULLになってしまうのです。

------------
id | spec1 | spec2 | ...(略) | spec10 | spec
------------
tanaka 4 1 (略)50 NULL

このような場合にどんなクエリを書いたらいいのしょうか。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+1

ability テーブルは

id, member_id, spec_id で構成する方が王道ですね。


テーブル定義

CREATE TABLE `abilities` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `member_id` int(11) DEFAULT NULL,
  `spec_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `abilities` (`id`, `member_id`, `spec_id`)
VALUES
    (1,1,1),
    (2,1,2),
    (3,2,1),
    (4,2,3),
    (5,2,4);

CREATE TABLE `member` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(641) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `member` (`id`, `name`)
VALUES
    (1,'tanaka'),
    (2,'suzuki');

CREATE TABLE `spec` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `spec` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `spec` (`id`, `spec`)
VALUES
    (1,'MySQL'),
    (2,'PHP'),
    (3,'LINUX'),
    (4,'C#');

SELECT

SELECT 
    m.id, m.name, group_concat(s.spec) AS specs 
FROM member m
LEFT JOIN abilities a ON m.id=a.member_id
LEFT JOIN spec s ON a.spec_id = s.id
GROUP BY m.id

テーブル定義(改)

優先順位の概念を追加
本来、正規化して以下のようにすべきかと思います。

CREATE TABLE `abilities` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `member_id` int(11) DEFAULT NULL,
  `spec_id` int(11) DEFAULT NULL,
  `priority` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

SELECT 
    m.id
    , m.name
    , group_concat(s.spec ORDER BY a.priority) AS specs 
FROM member m
LEFT JOIN abilities a ON m.id = a.member_id
LEFT JOIN spec s ON a.spec_id = s.id
GROUP BY m.id

事情を考慮した取得例

SELECT 
    a.name
    , sp1.spec as spec1
    , sp2.spec as spec2
    , sp3.spec as spec3
    , sp4.spec as spec4
    , sp5.spec as spec5
    , sp6.spec as spec6
    , sp7.spec as spec7
    , sp8.spec as spec8
    , sp9.spec as spec9
    , sp10.spec as spec10
FROM `member` AS a
LEFT JOIN `ability` AS b ON b.`id` = a.`ability_id`
LEFT JOIN `spec` AS sp1 ON sp1.`id` = b.`spec1`
LEFT JOIN `spec` AS sp2 ON sp2.`id` = b.`spec2`
LEFT JOIN `spec` AS sp3 ON sp3.`id` = b.`spec3`
LEFT JOIN `spec` AS sp4 ON sp4.`id` = b.`spec4`
LEFT JOIN `spec` AS sp5 ON sp5.`id` = b.`spec5`
LEFT JOIN `spec` AS sp6 ON sp6.`id` = b.`spec6`
LEFT JOIN `spec` AS sp7 ON sp7.`id` = b.`spec7`
LEFT JOIN `spec` AS sp8 ON sp8.`id` = b.`spec8`
LEFT JOIN `spec` AS sp9 ON sp9.`id` = b.`spec9`
LEFT JOIN `spec` AS sp10 ON sp10.`id` = b.`spec10`
WHERE  `id` = 1
group by a.id

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/12/30 23:12

    恐れ入りますが、テーブルの定義を私がやったように CREATE 文で提示してくれませんか?
    show create table テーブル名; で取得できます。

    キャンセル

  • 2016/12/30 23:27

    回答への追記ありがとうございます。
    このままだとスマートな解決ができなさそうなので、Kosuke_Shibuyaさんのご指摘通り、正規化も含めてテーブル定義全体の見直しをしてみようと思います。

    キャンセル

  • 2017/01/05 00:05

    横槍ですが。。
    テーブル構造をいじれる内なら皆様の語指摘のとおり正規化するのがベストです。

    そもそも同一項目を列もちして何がまずいかというと、
     ①下手をすれば項目増減が発生するたびにテーブル定義の変更が発生する
     ②今ケースの用に他テーブルを参照する必要がある場合横持ち分だけテーブル結合が必要
    の2点のデメリットが大きいです。

    特に①のようにテーブル定義をちょくちょく変える機会が発生する事態は、
    下手な事故を避ける意味でも必要最小限に抑えることが望ましいです。

    どうしても元のテーブルをいじれない状況の場合は、
    素直に項目数分テーブルを結合するクエリを書くか、
    列→行展開を行うビューを作成して逃げ道を作るかのどちらかになりますね。

    キャンセル

+1

こんな感じでは、(略は同じにしていますが。。。)

select m.id, t1.* from member m join 
( select a.id, 
max(case when spec1=s.id then spec else null end) spec1,
max(case when spec2=s.id then spec else null end) spec2,
...(略)
max(case when spec10=s.id then spec else null end) spec10
 from ability a cross join specs s where s.id in (spec1,spec2,...(略),spec10) 
 group by 1) t1
on ability_id=t1.id
where m.id=1;

+------+-------+-------+--------+
| id   | spec1 | spec2 | spec10 |
+------+-------+-------+--------+
|    1 | ruby  | php   | c++    |
+------+-------+-------+--------+
1 row in set (0.01 sec)

RDBは、配列形式の項目が得意ではないので、テーブルを変更されるのは良いと思います。
上記はちょっと面倒なのでもう少し簡単なのを(大量件数だとちょっと遅いですが)

select m.id,
(select spec from specs where specs.id=spec1) spec1,
(select spec from specs where specs.id=spec2) spec2,
...(略)
(select spec from specs where specs.id=spec10) spec10
  from member m join ability a  on ability_id=a.id where m.id=1;

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/12/30 23:19

    ありがとうございます。
    自分では思いつかないクエリで、とても参考になります。

    しかしこの構成だとけっこう複雑なクエリになってしまいますね・・・。
    やはりテーブル定義から見直すしかないかもしれません。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.52%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる