MySQLで記事のタグ検索で指定したタグを含む記事に関連した全てのタグを取得するSQL文を知りたい

Question

前提

以下のテーブル・データが存在している状態です。

ARTICLESテーブル（記事のテーブル）

ARTICLE_ID	ARTICLE_NAME
1	記事1
2	記事2
3	記事3
4	記事4
5	記事5

TAGSテーブル（タグのテーブル）

TAG_ID	TAG_NAME
1	タグ1
2	タグ2
3	タグ3
4	タグ4
5	タグ5

TAG_ARTICLE_RELATIONSテーブル（記事に紐づくタグのテーブル）

ID	ARTICLE_ID	TAG_ID
1	1	1
2	1	2
3	1	3
4	2	2
5	2	4
6	3	5
7	4	2
8	5	2
9	5	3

実現したいこと

記事のタグ(TAG_ID)検索で指定したタグを含む記事と記事(ARTICLE_ID)に関連した全てのタグ(TAG_ID)を取得するSQL文を知りたいです。

例えば、以下の条件で各結果を得られるSQL文を教えていただきたいです。

例１：全ての記事（条件なし）

ARTICLE_ID	TAG_IDS
1	1,2,3
2	2,4
3	5
4	2
5	2,3

例２：タグ2を含む全ての記事

ARTICLE_ID	TAG_IDS
1	1,2,3
2	2,4
4	2
5	2,3

例3：タグ1もしくはタグ3を含む全ての記事

ARTICLE_ID	TAG_IDS
1	1,2,3
5	2,3

実現したいことに対して
そもそもの取得方法やテーブル構造を変えた方が良いなどありましたら
その点もご教示いただけましたら幸いです。

試したこと

以下SQLを試してみましたが、「実現したいこと」の例２,例３と同様の取得結果が得られませんでした。

・例2：

SELECT
    TAG_ARTICLE_RELATIONS.ARTICLE_ID,
    GROUP_CONCAT(DISTINCT TAG_ARTICLE_RELATIONS.TAG_ID SEPARATOR ',') AS TAG_IDS
FROM
    TAG_ARTICLE_RELATIONS
WHERE
    TAG_ARTICLE_RELATIONS.TAG_ID in (2)
GROUP BY
    TAG_ARTICLE_RELATIONS.ARTICLE_ID
;

取得結果

ARTICLE_ID	TAG_IDS
1	2
2	2
4	2
5	2

↑指定したタグ以外のタグが取得できていない

・例３：

SELECT
    TAG_ARTICLE_RELATIONS.ARTICLE_ID,
    GROUP_CONCAT(DISTINCT TAG_ARTICLE_RELATIONS.TAG_ID SEPARATOR ',') AS TAG_IDS
FROM
    TAG_ARTICLE_RELATIONS
WHERE
    TAG_ARTICLE_RELATIONS.TAG_ID in (1, 3)
GROUP BY
    TAG_ARTICLE_RELATIONS.ARTICLE_ID
;

取得結果

ARTICLE_ID	TAG_IDS
1	1,3
5	3

↑こちらも指定したタグ以外のタグが取得できていない

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

MySQL 5.7.25

Answer 1

結構SQLの好みが分かれそうな問題やね
自分ならこうかな。テーブルの構造は特に問題ないんじゃないですかね。

select A.ARTICLE_ID
  ,(
    select group_concat(C.TAG_ID)
    from TAG_ARTICLE_RELATIOINS C
    where C.ARTICLE_ID=A.ARTICLE_ID
  ) TAG_IDS
from ARTICLES A
where exists(
  select 0
  from TAG_ARTICLE_RELATIOINS B
  where B.ARTICLE_ID=A.ARTICLE_ID
    -- and B.TAG_ID in (B.TAG_ID)  (1)またはこの行自体削除
    -- and B.TAG_ID in (2)         (2)
    -- and B.TAG_ID in (1,3)       (3)
)

Comments

[sousuke]

あ、終わってたｗ

[OoooOTAKONnnnN]

ご回答いただきましてありがとうございます。
ご教示下さった内容で実現したいことが実現できました、こういった方法もあるのですね！
勉強になります、ありがとうございます！

Answer 2

(1)

select ARTICLE_ID,group_concat(TAG_ID) as TAG_IDS
from TAG_ARTICLE_RELATIONS
group by ARTICLE_ID

(2)

select t1.ARTICLE_ID,group_concat(t1.TAG_ID) as TAG_IDS
from TAG_ARTICLE_RELATIONS as t1
inner join TAG_ARTICLE_RELATIONS as t2 on
t1.ARTICLE_ID=t2.ARTICLE_ID and t2.TAG_ID=2
group by ARTICLE_ID

(3)

select t1.ARTICLE_ID,group_concat(distinct t1.TAG_ID) as TAG_IDS
from TAG_ARTICLE_RELATIONS as t1
inner join TAG_ARTICLE_RELATIONS as t2 on
t1.ARTICLE_ID=t2.ARTICLE_ID and t2.TAG_ID in(1,3)
group by ARTICLE_ID

Comments

[OoooOTAKONnnnN]

ご回答いただきましてありがとうございます。
ご教示下さったSQL文で実行しまして、実現したいことが実現できました。
本当に助かりました、ありがとうございます！

Answer 3

↑指定したタグ以外のタグが取得できていない

...
WHERE
    TAG_ARTICLE_RELATIONS.TAG_ID in (2)
...

TAG_IDが2のデータに限定しているからですが、何か？

↑こちらも指定したタグ以外のタグが取得できていない

...
WHERE
    TAG_ARTICLE_RELATIONS.TAG_ID in (1, 3)
...

TAG_IDが1と3のデータに限定しているからです。
WHERE句の意味を理解できてていないのでは？

Comments

[OoooOTAKONnnnN]

そちらはあくまで試したことの一例を書いたまでなので、試したことに対する指摘ではなくどういったSQL文なら実現したいことをが実現できるかの解決方法を教えていただきたいです。

[Orlofsky]

すべてのTAG_IDを対象にしたいのならWHERE句を削るだけです。