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

Question

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

二つまでなら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

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

Answer 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;

Comments

[zico_teratail]

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

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

Answer 2

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

Comments

[zico_teratail]

ありがとうございます。
ability テーブルを参照するのが member テーブルだけであればそれで良いのですが、事情により他のテーブルからもability テーブルを参照することがあり、このようなテーブル構成になっております。

[退会済みユーザー]

DBの正規化が全くなされていないですよ。

[zico_teratail]

正規化はされていると思っているのですが、具体的にどこがどうダメなのでしょうか？

[退会済みユーザー]

DBの定義を追記してます。

[zico_teratail]

ありがとうございます。
おっしゃっている意味が分かりました。
ability テーブルにspec１～spec10まで並んでいる件が正規化できていない、という意味ですね？

これにつきましては、前述のように他のテーブルからの参照などいろいろ事情がありまして、苦肉の策でこのような形態になっております。

[退会済みユーザー]

経験上、どんな要件でも、正規化できないということを経験したことが全くないのですが、一体どんな事情があるのでしょう。全くイメージできないのでよろしければお聞かせください。

[zico_teratail]

私が質問文として例示した内容では分かりづらく、誤解を招く例で不適切だったかもしれません。
申し訳ありません。

実際のテーブル内容は　spec1～spec10の「並び順」というか「位置」も重要でして、たとえば
tanakaさんの
spec1＝4
spec2=1
と
hogeさんの
spec1＝1
spec2=4
は等価ではなく、全く違う意味となります。

[退会済みユーザー]

spec1 〜 spec10 には優先順位があるということですか？

[zico_teratail]

そうですね、優先順位と言ってもいいかもしれませんが、
たとえばspec1に1が入っている場合とspec2に1が入っている場合とでは意味合いが変わってきます。

[退会済みユーザー]

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

[zico_teratail]

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

[Panzer_vor]

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

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

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

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