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系でお願いします。)
date が新しいということは 一番最大ということでしょう。
なので 抽出するときに MAX(date) としたらいいですね。
で、ほか出力する内容で GROUP BY してあげたらいいです。
>memberテーブルのmember_idとbbsテーブルのmember_idが一致すれば表示されます。
分かっておられるとは思いますが、念のため。
LEFT JOIN
左側に指定された表のすべての行が表示されます。
------------
このJOINの仕方では、dateがNULLの場合もありえるので、
MAXとか使っても期待通りの結果があれらない場合があります。
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の値が一意にならないなら、結果を保証しないと明記されています。
なるほど・・。この例で言うとC2は一意にならないので、私が求めている結果にはなりませんね。
ただ、求めている結果で出力されて欲しいので、どうしたらよいのか悩むところです。
特定の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`
テンポラリテーブルを使って上手くできました!!
こんな便利な機能があったんですね。だいぶ使い方の幅が広がったと思います。
ソースもいただき、ありがとうございました。
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
ありがとうございます。ただ実際には複数テーブルをLEFT JOINで結合しているのですが、
そうするとなぜかMIN/MAXで抽出されず、登録IDが古い方のデータが表示します。
(上の例ならbbs_idが1のデータが表示される)
質問の例だけではなく、複数テーブルを結合した場合でも
目的の結果が得られる方法(または得られない場合の注意点)なども
教えていただければと思います。