テーブル結合でGROUP_CONCATを使用する際、SELECTカラムに応じて他のカラムの結果が変わるのはなぜ？

Question

前提

テーブルatable
・idカラム
・titleカラム

テーブルbtable
・b_idカラム
・b_titleカラム

テーブル関係
・1つのatableに対して複数btable

---

最終的にやりたいこと

atableのidカラム毎に以下の内容を取得したい
・1つのatable1レコードに対して、btableのレコードが2つあった場合
(
[id] => 1
[title] => atableのレコード1内容
[b_title] => btableのレコード9内容,btableのレコード23内容

---

期待した結果を取得できたSQL

SELECT 
  a.id,
  r.b_id,
  GROUP_CONCAT(b_title) AS b_title
FROM atable AS a
LEFT OUTER JOIN( 
  SELECT
    b_id,b_title
  FROM
    btable
) AS r ON a.id = r.b_id

GROUP BY a.id

---

期待した結果を取得できないSQL1
・SELECT で、r.b_idを外すと、a.idの結果がおかしくなる(上記と比較すると、a.idの取得値が変更される。何の値かは不明)

SELECT 
  a.id,
  GROUP_CONCAT(b_title) AS b_title
FROM atable AS a
LEFT OUTER JOIN( 
  SELECT
    b_id,b_title
  FROM
    btable
) AS r ON a.id = r.b_id

GROUP BY a.id

---

期待した結果を取得できないSQL2
・SELECT で、a.idを外すと、r.b_idの結果がおかしくなる(r.b_idの取得値が変更される。何の値かは不明)

SELECT 
  r.b_id,
  GROUP_CONCAT(b_title) AS b_title
FROM atable AS a
LEFT OUTER JOIN( 
  SELECT
    b_id,b_title
  FROM
    btable
) AS r ON a.id = r.b_id

GROUP BY a.id

Comments

[dupont_kedama]

「おかしくなる」というのが具体的にどんなことなのかがわからないです。

Answer 1

GROUP_CONCATの使い方がおかしくないですか？
GROUP_CONCAT(カラム名)は、GROUP_BY句でグループ化した行の中から、指定したカラムの値を連結して返す関数です。

Comments

[re97]

最終的にやりたいことを満たすためには、具体的にどう記述すれば良いでしょうか？

[sonor_3rd]

スマホなのでヒントだけ。btableにatableをjoinして、a.idをgroup byでまとめてください。これでb_titleが複数行まとまる形になると思うので、concatで連結できるかと。

[sonor_3rd]

あ、btableにはaのidがないのか。失礼しました

[sonor_3rd]

元のテーブルを(select 任意カラム from atable,btable where atable.b_id=btable.id)にしてgroup by a.idとconcatすると幸せになれるかも

[sonor_3rd]

SELECT
id,
title,
GROUP_CONCAT(b_title)
FROM
(SELECT
atable.id AS id,
atable.title AS title,
btable.b_title AS b_title
FROM
atable,
btable
WHERE
atable.id = btable.b_id
)
GROUP BY id

これでいかがでしょうか？

[re97]

＞あ、btableにはaのidがないのか。失礼しました
・「b_id」カラムは、「aのid」カラムの値です

[sonor_3rd]

上でだめならこれです。

SELECT
a.id AS id,
a.title AS title,
GROUP_CONCAT(b.b_title) AS b_title
FROM
btable b
LEFT JOIN atable a
ON (b.b_id = a.id)
GROUP BY a.id

[re97]

おお！ いけました！
なぜでしょう？
ON の後に（）が必要だったということでしょうか？？？

[sonor_3rd]

すみません。知識不足のため原因までは解りませんが、括弧は問題ではないはずです。

テーブル構成をみて、LEFT OUTER JOINが気になりました。
RIGHT OUTER JOINで同様の症状が起きるか、検証していただけますか？

[re97]

・「RIGHT OUTER JOIN」など色々試した結果、ようやく原因が分かりました
・「何の値か不明」と書きましたが、よくよくよくよく調べてみたらそういうレコードがちゃんと存在していました
・全て私の勘違いでした(非常に良く似たデータが複数あり、そのことを全く認識していなかったため)
・最終的に、当初のSQL文でも回答いただいたSQLでも、期待した結果を取得できることを確認できました

・何回も質問してしまいましたが、おかげで勘違いに気がつくことができました
・貴重な数日間、お付き合いいただきありがとうございました

[sonor_3rd]

余談ですが・・・btableをb_idでグルーピングした後でatableと結合する手法もありますね。
問題解決できてなによりです。

Answer 2

「おかしくなる」原因がぱっと思いつかないのですのですが、
テーブル結合についてちょっと気になるので提案です。

シンプルに

SELECT 
  a.id,
  GROUP_CONCAT(b_title) AS b_title
FROM atable AS a
LEFT JOIN btable AS b ON a.id = b.b_id
GROUP BY a.id

ではダメでしょうか？

もし質問文に記載されていない（サブクエリでなければならないような）条件があるなら、それも可能な範囲で提示してください。

Comments

[re97]

回答ありがとうございます。
・試してみましたが、結果は変わりませんでした
・SELECT a.id,だとよく分からない値になり
・SELECT a.id,b.b_idだと期待した値を取得できます

[dupont_kedama]

「,GROUP_CONCAT(b_title) AS b_title」
がなければ a.id だけでも期待した値を取得できるのでしょうか？

[re97]

「,GROUP_CONCAT(b_title) AS b_title」を削除すると、 a.id だけで期待した値を取得できます