mysqlで内部結合をし、order byとgroup byを併用する時の書き方が知りたいです。

Question

###前提・実現したいこと
mysqlで内部結合をし、order byとgroup byを併用する時の書き方が知りたいです。

テーブルは、「chat」「chat_messa」と言うテーブルを作りました。
「chat」の「id」と「chat_messa」の 「chat_id」で結合しました。

chatテーブルのデータ
+----+-------------+
| id | messa |
+----+-------------+
| 1 | こんにちは |
| 2 | こんばんは |
| 3 | さようなら |
+----+-------------+

chat_messaテーブルのデータ
+---------+-------------------------+
| chat_id | updated_date |
+---------+-------------------------+
| 1 | 2016-07-24 11:44:27 |
| 1 | 2016-07-24 19:42:42 |
| 2 | 2016-07-25 10:27:37 |
| 2 | 2016-07-29 13:45:59 |
| 3 | 2016-07-27 00:13:33 |
| 3 | 2016-07-29 08:13:13 |
+---------+-------------------------+

結合後のデータでは、下記のように取得したいのですが、
+----+-------------+---------+-------------------------+
| id | messa | chat_id | updated_date |
+----+-------------+---------+-------------------------+
| 3 | さようなら | 3 | 2016-07-29 08:13:13 |
| 2 | こんばんは | 2 | 2016-07-29 13:45:59 |
| 1 | こんにちは | 1 | 2016-07-24 19:42:42 |
+----+-------------+---------+-------------------------+

order byとgroup byを併用すると、下記のようになってしまします。
+----+-------------+---------+-------------------------+
| id | messa | chat_id | updated_date |
+----+-------------+---------+-------------------------+
| 3 | さようなら | 3 | 2016-07-27 00:13:33 |
| 2 | こんばんは | 2 | 2016-07-25 10:27:37 |
| 1 | こんにちは | 1 | 2016-07-24 11:44:27 |
+----+-------------+---------+-------------------------+

###発生している問題

order byとgroup byを併用する時には、サブクエリを使えば良いのはわかったのですが、テーブルの内部結合をした場合にはどう書いたらいいのかわからずに困っています。

###該当のソースコード

テーブルの内部結合なしなら、これで出来ました。
select * from (select * from chat_messa order by updated_date desc) as subquery group by chat_id order by id desc

テーブルの内部結合をした場合が分かりません。

###試したこと
テーブルの内部結合なしなら、これで出来ました。
select * from (select * from chat_messa order by updated_date desc) as subquery group by chat_id order by id desc

###補足情報(言語/FW/ツール等のバージョンなど)
mysql5.5

Answer 1

結合後のテーブルから推測しますと
＜仕様＞
chat_messaテーブルに有る最新日付のchat_idを抽出して、chatテーブルの文言を合わせてchar_idの大きい順に表示します。

select t2.id, t2.messa, t1.chat_id, t1.update_date from
 (select chat_id, max(udate_date) update_date from chat_messa group by chat_id) t1
 join chat t2 on t1.chat_id=t2.id
 order by 1 desc;

Comments

[hiroppii]

回答有難う御座います。

A.Ichi さんの SQLを実行させて頂いたところ、updated_date が新しいもので取得出来ました。

ありがとうございました。

Answer 2

SELECT id, messa, chat_id, updated_date FROM chat
INNER JOIN chat_messa ON chat.id = chat_messa.chat_id
ORDER BY id DESC

テーブルの関係が、1:1 なのか、1:n なのか不明だし、提示された例だと group にする意味も不明なので、これ以上説明不可。

SELECT id, messa, chat_id, max(updated_date) as updated_date FROM chat
LEFT JOIN chat_messa ON chat.id = chat_messa.chat_id
GROUP BY chat_messa.chat_id
ORDER BY chat.id DESC

Comments

[hiroppii]

回答有難う御座います。

テーブルの関係が、1:1です。
chat_messaテーブルには、複数のchat_idが格納されているので、groupで、一つにまとめる必要があります。

また、テーブルの様子を書き加えました。

[退会済みユーザー]

> chat_messaテーブルには、複数のchat_idが格納されているので、groupで、一つにまとめる必要があります。

それなら 1:n では？

[hiroppii]

すみません。勘違いしていました。
1:nです。

[退会済みユーザー]

あと、複数あるなら、updated_date をどのようなルールで取り出すのか不明。最小値なのか、最大値なのか。

[hiroppii]

updated_date のなかでより新しいものを取得したいので、最大値になるかと思います。

[退会済みユーザー]

回答に追記しておきました

[hiroppii]

ありがとうございます。
回答の通り実行させて頂いたのですが、updated_dateの部分が古いものが出て来てしまいます。

[退会済みユーザー]

updated_date　の型が適切ではないのでは？おおかた、varchar で設定でもしているのでは？

[hiroppii]

updated_date の 型は、datetime になっていました。

[退会済みユーザー]

datetime なのに、2016-07-29 とデータが入っていることに矛盾します。正確な情報を提供してください。

[hiroppii]

データの情報を変更させて頂きました。

[退会済みユーザー]

だとしても、先の回答で問題ないはずですよ。それでも最大値が取れないんでしたら、他の問題かと思います。

[hiroppii]

わかりました。
有難う御座います。
いつも有難う御座います。

Answer 3

おそらく今回のケースはサブクエリでつなぐのではないと思います
またサブクエリの中でorder byしていますが意味がわかりません

group byするときはselect句が*はありえないので
きちんと集計キーと集計方法を記載してください

追記

例示のデータを利用するとこうかな？

select * from chat inner join 
(select chat_id,max(updated_date) as updated_date from chat_messa group by chat_id) as sub
on chat.id=sub.chat_id order by id desc;

Comments

[hiroppii]

回答有難う御座います。

内部結合をした後に、updated_dateカラムの降順で取得したいと思っています。

また、データベースの構造を書き足しました。

[yambejp]

追記しときました

[hiroppii]

ありがとうございます。

実行させて頂いたのですが、updated_dateの部分が古いものが出て来てしまいます。