MySQLで1対多の 多のテーブルを複数JOINして結果を得る方法。

Question

実現したいこと

テーブル構成

locations (小売やレストラン情報)

• id (主キー)
• name

categories (locationsのカテゴリー)

• id (主キー)
• name

tags (locationsのタグ)

• id (主キー)
• name

location_categories (中間テーブル)

• location_id (複合主キー)
• category_id (複合主キー)

location_tags (中間テーブル)

• location_id (複合主キー)
• tag_id (複合主キー)

上記のような5つのテーブルがあります。

locationsに対して
カテゴリーやタグが1対多の関係で保存しています。

locationsテーブルからリストを習得する際に、
カテゴリーやタグ情報を含めた形で一覧データを取得するのが目的です。

locations.name	category_names	tag_names
○×庵	蕎麦, うどん	出前可, トイレ
トラットリア FooBar	イタリアン, ピザ, ワイン	予約,テイクアウト, トイレ
Cafe Example	カフェ	予約,テイクアウト
Hoge家	ラーメン	NULL
Burger Test	ファストフード	ドライブスルー, テイクアウト

現在、GROUP_CONCATで結合した結果の取得を試してましたので
上記のような形でデータを取得したいと考えております。

試したこと

locationsテーブルへ関連テーブルをLEFT JOIN してみました。

 SELECT 
  locations.id, 
  locations.name,
  GROUP_CONCAT(tags.name) AS tag_names,
  GROUP_CONCAT(categories.name) AS category_names
 FROM locations 
 LEFT JOIN location_tags          ON locations.id = location_tags.location_id 
 LEFT JOIN tags                   ON location_tags.tag_id = tags.id
 LEFT JOIN location_categories    ON locations.id = location_categories.location_id 
 LEFT JOIN categories             ON location_categories.category_id = categories.id
 GROUP BY locations.id 

実行結果は以下

id	name	tag_names	category_names
2	○×庵	出前,トイレ,出前,トイレ	そば,うどん,うどん,そば
3	トラットリア FooBar	予約,トイレ,トイレ,テイクアウト,テイクアウト,予約,トイレ,テイクアウト,予約	イタリアン,ワインバー,イタリアン,ワインバー,イタリアン,ピザ,ピザ,ピザ,ワインバー
4	Cafe Example	予約,テイクアウト	カフェ,カフェ
5	Hoge家	NULL	ラーメン
6	Burger Test	ドライブスルー,テイクアウト	ファストフード,ファストフード

こちらの実行結果は、LEFT JOIN を重ねている為カテゴリーやタグの数に応じて
それぞれタグやカテゴリーが重複してしまいました。

これらの重複なくデータを習得する方法がございましたら、
教えていただきたいです。
よろしくお願い致します。

補足情報

MySQL 5.7

サンプルデータ

CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

INSERT INTO `categories` VALUES (1,'そば'),(2,'うどん'),(3,'ラーメン'),(4,'カフェ'),(5,'ファストフード'),(6,'ピザ'),(7,'ワインバー'),(8,'居酒屋'),(9,'イタリアン');


CREATE TABLE `location_categories` (
  `location_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  UNIQUE KEY `location_id` (`location_id`,`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `location_categories` VALUES (1,4),(2,1),(2,2),(3,6),(3,7),(3,9),(4,4),(5,3),(6,5);


CREATE TABLE `location_tags` (
  `location_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  UNIQUE KEY `location_id` (`location_id`,`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `location_tags` VALUES (1,3),(1,4),(2,3),(2,4),(3,1),(3,4),(3,5),(4,1),(4,5),(6,1),(6,2);


CREATE TABLE `locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

INSERT INTO `locations` VALUES (2,'○×庵'),(3,'トラットリア FooBar'),(4,'Cafe Example'),(5,'Hoge家'),(6,'Burger Test');

CREATE TABLE `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

INSERT INTO `tags` VALUES (1,'テイクアウト'),(2,'ドライブスルー'),(3,'出前'),(4,'トイレ'),(5,'予約'),(6,'貸し切り');

Comments

[yambejp]

create table+ insert でサンプルデータを提示ください

[wasabin]

コメントありがとうございます。

サンプルデータを追記しました。
よろしくお願い致します。

Answer 1

サンプルの提示が無いので未検証ですがとりあえずダブリがいやなら
GROUP_CONCAT内にDISTINCTを指定できます

GROUP_CONCAT(tags.name)
↓↓↓
GROUP_CONCAT(DISTINCT tags.name)

もっと効率的な処理が必要かもしれませんが、とりあえずダブりはなくなるでしょう

追記

sample確認しました。
これはtagとcategoryを別々に処理してサブクエリでつなぐのが楽でしょう

SELECT *
FROM locations as t1
left join(
/* ここからtagまとめ */
select t2.location_id as id,group_concat(t3.name) as tags
from location_tags as t2
inner join tags as t3 ON t2.tag_id = t3.id
group by t2.location_id
/* ここまで */
) as t6 using(id)
left join (
/* ここからcategoryまとめ */
select t4.location_id as id,group_concat(t5.name) as categories
from location_categories as t4
inner join categories as t5 ON t4.category_id = t5.id
group by t4.location_id
/* ここまで */
) as t7 using(id)
order by id

Comments

[yambejp]

sample確認して追記しました

[wasabin]

ご回答ありがとうございます。
期待する結果を得ることができました。

この場合、個々をサブクエリでつなげると良いのですね。
勉強になりました。
ありがとうございます！