MySQLのJOINを使って結合する方法について質問します。


SELECT * FROM member LEFT JOIN bbs ON member.member_id=bbs.member_id

とした場合、memberテーブルのmember_idとbbsテーブルのmember_idが一致すれば表示されます。

次に「GROUP BY member.member_id」とした場合、bbsに同じmember_idが複数あっても1レコードしか表示されなくなります。(つまり、bbs内の重複したmember_idは表示されない)

そこで質問ですが、結合するbbsテーブルの登録状況が以下の通りだとします。
[bbs_id][member_id][date]
[1][1][2007-10-18]
[2][1][2007-10-19]

上記のSQLで結合すると、bbs_idが1のデータが表示されると思うのですが、dateの新しいbbs_idが2の方を表示させるにはどうしたらいいのでしょうか?

”テーブル同士を結合して、かつグループ化して結合先のレコードは1レコードしか表示しない。
しかしその表示するのは、特定の条件に一致したもの(ORDER BY bbs.date DESC など)”

という事をする為のSQL文の組み立て方を教えていただければと思います。
(MySQLは4.0x系でお願いします。)

回答の条件
  • 1人2回まで
  • 登録:2007/10/18 08:41:23
  • 終了:2007/10/19 23:33:13

回答(5件)

id:taknt No.1

きゃづみぃ回答回数13539ベストアンサー獲得回数11982007/10/18 10:24:38

ポイント10pt

date が新しいということは 一番最大ということでしょう。

なので 抽出するときに MAX(date) としたらいいですね。

で、ほか出力する内容で GROUP BY してあげたらいいです。

id:kt26

ありがとうございます。ただ実際には複数テーブルをLEFT JOINで結合しているのですが、

そうするとなぜかMIN/MAXで抽出されず、登録IDが古い方のデータが表示します。

(上の例ならbbs_idが1のデータが表示される)


質問の例だけではなく、複数テーブルを結合した場合でも

目的の結果が得られる方法(または得られない場合の注意点)なども

教えていただければと思います。

2007/10/18 18:40:02
id:KUROX No.2

KUROX回答回数3542ベストアンサー獲得回数1402007/10/18 19:07:19

ポイント10pt

>memberテーブルのmember_idとbbsテーブルのmember_idが一致すれば表示されます。

分かっておられるとは思いますが、念のため。

LEFT JOIN

左側に指定された表のすべての行が表示されます。

------------

このJOINの仕方では、dateがNULLの場合もありえるので、

MAXとか使っても期待通りの結果があれらない場合があります。

id:chuken_kenkou No.3

chuken_kenkou回答回数722ベストアンサー獲得回数542007/10/18 20:18:49

ポイント10pt

MySQLは、GROUP BYに関して、拡張仕様があるので注意が必要です。

MySQL AB :: MySQL 4.1 リファレンスマニュアル :: 6.3.7.3 非表示のフィールドに対する GROUP BY

標準SQLや主要なRDBMSでは、以下のような記述はエラーになります。

select c1,c2,c3
 from t1
 group by c1

しかし、MySQLでは、エラーにしていません。C1をグループ化することで、C2、C3の値も一意になるなら、

この構文を使用してよいことになっています。

一方、C1をグループ化しても、C2、C3の値が一意にならないなら、結果を保証しないと明記されています。

id:kt26

なるほど・・。この例で言うとC2は一意にならないので、私が求めている結果にはなりませんね。

ただ、求めている結果で出力されて欲しいので、どうしたらよいのか悩むところです。

2007/10/18 20:47:12
id:chuken_kenkou No.4

chuken_kenkou回答回数722ベストアンサー獲得回数542007/10/19 17:51:31

ポイント100pt

特定のmember_idについて、結果を得るなら、「LIMIT n」を使う方法はいかがでしょうか?

SELECT *
 FROM `member`
  LEFT JOIN bbs
   ON member.member_id=bbs.member_id
 WHERE member.member_id=1
 ORDER BY bbs.`date` DESC
  LIMIT 1;

複数のmember_idについて、結果を得たい場合は、MySQL 4.0系だと、一時表(テンポラリ・テーブル)を

利用するといった方法になります。


-- 一時表の定義
CREATE TEMPORARY TABLE wbbs
SELECT member_id,MAX(`date`) as `date`
 FROM bbs
 GROUP BY member_id,bbs_id;

-- 検索
SELECT *
 FROM `member`
  LEFT JOIN wbbs
   ON `member`.member_id=wbbs.member_id
  LEFT JOIN bbs
   ON wbbs.member_id=bbs.member_id
      and wbbs.`date`=bbs.`date`
id:kt26

テンポラリテーブルを使って上手くできました!!

こんな便利な機能があったんですね。だいぶ使い方の幅が広がったと思います。

ソースもいただき、ありがとうございました。

2007/10/19 23:31:19
id:tail_furry No.5

敷守ほむら回答回数74ベストアンサー獲得回数72007/10/19 16:06:36

ポイント10pt

SQL Serverで確認したので、MySQLではできないかもしれませんが…

select * 
from member C 
left join (
select A.bbs_id, A.member_id, A.[date] 
from bbs A 
inner join (select max(bbs_id) as bbs_id, member_id from bbs group by member_id) B 
on B.bbs_id = A.bbs_id
) D 
on C.member_id = D.member_id
  • id:b-wind
    > GROUP BY member.member_id
    としてる時点で本来 member_id と集合関数以外は表示できない。

    > 登録IDが古い方のデータが表示
    なのは MySQL の独自拡張が原因では?
    http://dev.mysql.com/doc/refman/4.1/ja/group-by-hidden-fields.html
    >> GROUP BY 部分から取り除くカラムがグループ内で一意なものではない場合は、この機能を使用しないでください。予測不可能な結果になります。
    なので制御不能。

    4.0 系ではサブクエリも使えないので、すなおに2クエリ発行して対処すべきだと思う。
  • id:kt26
    コメントありがとうございます。
    ただ、「2クエリ発行して対処」という意味がいまいち分かりません。。。
  • id:b-wind
    >「2クエリ発行して対処」という意味がいまいち分かりません。。。
    SQLを2回に分けて実行するということです。

    1回目で user_id 毎に dateの新しいbbs_id を取得する SQL 文を実行。
    プログラムで取得するか、テンポラリテーブルに格納するとよいでしょう。

    2回目でそのデータをもとに実際に取得したい SQL 文を流します。
    WHERE に先の条件を列挙するか、テンポラリテーブルとの JOIN することになります。

    MySQL4.1 以降であればサブクエリが使えるのでこれを1回で済ますことができますが、
    4.0 ではこういう方法しかないです。
  • id:kt26
    b-windさん

    chuken_kenkouさんと同じく、テンポラリテーブルで対処出来ました。
    アドバイスありがとうございました。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません