WHEREで絞り込みつつ、他のカラムを取得したい

Question

前提

・MySQLで「タグを持つ投稿」を取得しています。
・テーブル構造は変えずにお願い致します。

実現したいこと

「指導」というタグを指定しつつ、その投稿が持つ他の「鈴木」「佐藤」というタグも取得したいです。次の結果を目指しています。

comment_id	comment	genre_names	author_name
1	眠らないでください	指導	鈴木,佐藤
3	席についてください	指導	(null)

発生している問題

「鈴木」「佐藤」というタグが取得されず、次のようにnullとなってしまいます。

comment_id	comment	genre_names	author_name
1	眠らないでください	指導	(null)
3	席についてください	指導	(null)

該当のソースコード

テーブル構造です。これは変えたくありません。

CREATE TABLE my_comments
(`ID` int, `comment` varchar(10));
INSERT INTO my_comments
(`ID`, `comment`)
VALUES
(1,  '眠らないでください'),
(2,  '静かにしてください'),
(3,  '席についてください'),
(4,  'たまに休んで下さい');

CREATE TABLE my_tags
(`ID` int, `tag_kind_id` int, `tag_name` varchar(100));
INSERT INTO my_tags
(`ID`, `tag_kind_id`, `tag_name`) # tag_kind_id=1 なら genre で、tag_kind_id=2 なら author などなど
VALUES
(1,  2,  '鈴木'),
(2,  2,  '佐藤'),
(3,  1,  '指導');

CREATE TABLE my_tag_holders
(`comments_ID` int, `tags_ID` int);
INSERT INTO my_tag_holders
(`comments_ID`, `tags_ID`)
VALUES
(1, 1),(1, 2),(1, 3),
(2, 4),
(3, 3);

問題の生じるSELECT文です。これを改善したいです。
（右記でお試しいただけます → http://sqlfiddle.com/#!9/b95e945/1 ）

SELECT	comments.ID AS comment_id
		,comments.comment

		# ジャンル
		,GROUP_CONCAT(
			CASE WHEN tags.tag_kind_id=1 THEN tags.tag_name
			ELSE null END
		) AS genre_names	
        
		# 人
		,GROUP_CONCAT(
			CASE WHEN tags.tag_kind_id=2 THEN tags.tag_name
			ELSE null END
		) AS author_name	

FROM	my_comments comments
		LEFT JOIN my_tag_holders th
				ON th.comments_ID = comments.ID 
		LEFT JOIN my_tags tags
				 ON tags.ID = th.tags_ID 
                       
-- タグ名で指定          
WHERE tags.tag_name = '指導'
GROUP BY comments.ID    
LIMIT 0, 5

試したこと

IDで指定してみると取得できました。

-- IDで指定          
WHERE comments.ID IN ( 1, 3 )
GROUP BY comments.ID    
LIMIT 0, 5

そのためtag_nameをWHEREに指定する部分が原因だとわかります。

そこで、「もしかして絞り込まれてしまった後に他のタグは取得できないのか？」と考え、絞り込みのためのJOINを追加して次のSELECTを実行したところ、なんとか目的の結果を得ることができました。

FROM	my_comments comments
		LEFT JOIN my_tag_holders th
				ON th.comments_ID = comments.ID 
		LEFT JOIN my_tags tags
				ON tags.ID = th.tags_ID 

		-- 絞り込みのためのJOINを追加
		LEFT JOIN my_tag_holders th2
				ON th2.comments_ID = comments.ID 
		LEFT JOIN my_tags tags2
				ON tags2.ID = th2.tags_ID 
                  
-- タグ名で指定          
WHERE tags2.tag_name = '指導'
GROUP BY comments.ID    
LIMIT 0, 5

しかし、「このようなJOINは二度手間ではないか？」と疑念を持っています。
より正しい方法があるのでは…と質問させて頂きました。

補足情報（FW/ツールのバージョンなど）

MySQLのバージョンはやや古めな5.7を使用しております。
よろしくお願い致します。

その後

INNER JOIN で検索結果を絞る、以下の書き方が最速でした

FROM	my_comments comments

		INNER JOIN (
	
			SELECT comments2.ID
			FROM   my_comments comments2
			   	   LEFT JOIN my_tag_holders th2
						     ON th2.comments_ID = comments2.ID 
			   	   LEFT JOIN my_tags tags2
						      ON tags2.ID = th2.tags_ID 	

			-- タグ名で指定  
			WHERE  tags2.tag_name = '指導'
			GROUP BY comments2.count_likes, comments2.ID
			ORDER BY comments2.count_likes DESC, comments2.ID DESC
			LIMIT 0, 20
		
		) AS searched ON searched.ID = comments.ID

		LEFT JOIN my_tag_holders th
				ON th.comments_ID = comments.ID 
		LEFT JOIN my_tags tags
				ON tags.ID = th.tags_ID 

GROUP BY searched.ID
ORDER BY comments.count_likes DESC, comments.ID DESC
LIMIT 0, 20

Comments

[yambejp]

テーブル構造がおかしくロジックが破綻していませんか？
「席についてください」と「指導」はどういったリレーションなのでしょうか？

[nikuatsu]

「席についてください」は、my_tag_holders テーブルの (3, 3) レコードによって、「指導」というタグと関連付けられています。

すみません。伝わりますでしょうか…
別の言い方をすると、

「席についてください」が「my_comments.ID=3」で、「指導」が「my_tags.ID=3」なので、my_tag_holders テーブルの (3, 3) レコードでそれらが関連づけられています。

Answer 1

一応こんな感じで

select t1.ID,
max(t1.comment) as comment,
group_concat(t3.tag_name) as genre_names,
group_concat(t4.tag_name) as author_name
 from my_comments as t1
left join my_tag_holders as t2 on t1.ID=t2.comments_id
left join my_tags as t3 on t2.tags_id=t3.ID and t3.tag_kind_id=1
left join my_tags as t4 on t2.tags_id=t4.ID and t4.tag_kind_id=2
group by ID
having genre_names is not null or author_name is not null 

my_tag_holders調整

create table my_tag_holders2
(pid int primary key,cid int not null,gid int,aid int null,index(cid,gid,aid));

insert into  my_tag_holders2 values
(1,1,3,1),
(2,1,3,2),
(3,3,3,null);

select t1.ID,
max(comment) as comment,
t3.tag_name as genre_name,
group_concat(t4.tag_name) as author_name
from my_comments as t1
inner join my_tag_holders2 as t2 on t1.ID=t2.cid
inner join my_tags as t3 on t2.gid=t3.ID
left join my_tags as t4 on t2.aid=t4.ID
group by ID,genre_name;

Comments

[yambejp]

ちょっとサンプルデータが少ないので例外処理とかどこまで必要なのかわからないですね

[nikuatsu]

ありがとうございます。サンプルが少なくて申し訳ございません。

”「タグを持つ投稿」を取得” と書いたのは、「指導」のタグを持つコメントや、「鈴木」のタグを持つコメント、のように、あくまでタグ指定での取得を目指しているという意味でして・・・

[yambejp]

my_tag_holdersがちょっと不自由なデータの持ち方ですよね
たとえば鈴木＝指導の組み合わせがあっても佐藤＝指導の組み合わせがNGとか表現できないです
ちゃんとやるならmy_tag_holdersをcomment_id,genre_id,author_idの組み合わせとスべきです

[nikuatsu]

やはりそのように別カラムに分けるべきでしたか…ご助言ありがとうございます。別カラムに分けるとnullや0が入ってしまうので避けた方がいいかと思い現状の構造にしたのですが…

（例えばあるタグがgenreであるときに、genre_idのカラムだけが埋まり、author_idのカラムがnullや0になってしまうという問題です。そしてtag_kind_idは実際は６種類ありますので、5つのカラムはnullや0になってしまうのです。）

…ですが、nullや0が入ることよりも、ご指摘のような不自由さが重大な問題だということですね。ありがとうございます。テーブル構造の見直しも検討してみます。

[nikuatsu]

思ったのですが、その場合インデクスはどうされますか？

質問の場合ですと
tags_ID,comments_ID
だけで済みますが、

その場合ですと
genre_id,comments_ID
author_id,comments_ID
のように、tag_kind_idの数だけベタベタ貼りまくることにならざるをえないでしょうか？

[yambejp]

とりあえず追記しておきました
genreとauthorの組み合わせが必要かどうかもわからないのでご提示のソースが
ベストかどうかもよくわかりません

[nikuatsu]

「指導」のタグを持つコメントや、「鈴木」のタグを持つコメント、のように、あくまでタグ指定での取得を目指しているのですが、追記にはそれらの指定がなく…ご意図がよく掴みきれませんでした。すみません。

ひとまず当初にご回答いただきました having など参考にしながら引き続き解決に取り組んで参ります。

[yambejp]

質問者さんがわからない以上に回答者は状況がわからないので最大限想像して書いています。
きちんと仕様を提示して、どういうデータからどういうロジックでどういう結果をほしいか
サンプルをつけて質問してください

[nikuatsu]

たしかにそうですよね…。分かりにくい質問で申し訳ございませんでした。

Answer 2

HAVINGで目的の結果になると思うけど、よいテーブル定義かと
言われれば決してそうではないと思う。

-- WHERE tags.tag_name = '指導'
GROUP BY comments.ID
HAVING genre_names='指導'
LIMIT 0, 5

追記

genre_namesかどうか不定ならexists内のtag_nameで対処
２回連結するっていうのはその通り。

where exists(
  select 0
  from my_tag_holders h
    inner join my_tags t on t.ID=h.tags_ID
  where h.comments_ID=comments.ID
    and t.tag_name='指導'
)
GROUP BY comments.ID
LIMIT 0, 5   

Comments

[nikuatsu]

ありがとうございます。せっかくですが、genreかどうかは不定でして、あくまで「タグ名の指定」でのSELECTを考えています。「指導」というgenreでなく、「鈴木」というauthorの指定でのSELECTかもしれないということです。

[nikuatsu]

「指導」は検索ボックスへの入力値とお考え頂ければと思います。入力時点でそれがgenreかauthorかは不定で、もしかしたら指導さんというauthorもいるかもしれません。

[nikuatsu]

追記、拝見しました。ありがとうございます。「exists」句というのは知らなかったです。