MAX関数を使ってからLEFT JOINしたいのですが・・

質問

毎度お世話になります。
下記２つのテーブルからテーブル結合をして表示させたいのですが、その前にサブテーブルにある時間データの中で最新のものだけと結合したいと考えています。色々試したのですがうまくいきません。どなたかご教授の程よろしくお願いいたします。

main_tbl
| id | basyo | name |
---------------------
| 1　|　1　　| aa　　|
| 2　|　1　　| bb　　|
| 3　|　2　　| cc　　|
| 4　|　3　　| dd　　|

sub_tbl
| id | jyotai |　time　　|
------------------------
| 1　|　1　　　|12:25:30|
| 1　|　2　　　|13:15:12|
| 2　|　1　　　|13:20:14|
| 1　|　3　　　|13:50:08|

欲しい結果
| id | basyo | name | jyotai |　time　|
---------------------------------------
| 1　|　1　　| aa　　|　3　　　|13:50:08|
| 2　|　1　　| bb　　|　1　　　|13:15:12|
| 3　|　2　　| cc　　| null　　|　null　|
| 4　|　3　　| dd　　| null　　|　null　|

考えた構文
$sql = "SELECT main_tbl.id, main_tbl.basyo, main_tbl.name, sub_tbl.jyotai, sub_tbl.time FROM main_tbl LEFT OUTER JOIN (SELECT MAX(time) FROM sub_tbl GROUP BY id) ON main_tbl.id = sub_tbl.id";
または
$sql = "SELECT main_tbl.id, main_tbl.basyo, main_tbl.name, sub_tbl.jyotai, sub_tbl.time FROM main_tbl LEFT OUTER JOIN sub_tbl on main_tbl.id = sub_tbl.id WHERE SELECT MAX(time) FROM sub_tbl GROUP BY id";
または
$sql1 = "SELECT MAX(time) FROM sub_tbl GROUP BY id";
$rs1 = mysql_db_query($db,$sql1) or die("sql Error!");
$sql = "SELECT main_tbl.id, main_tbl.basyo,main_tbl.name, $rs1.jyotai, $rs1.time FROM main_tbl LEFT OUTER JOIN $rs1 ON main_tbl.id = $rs1.id";

～以下クエリの実行と表示文～

どれも駄目でした。（クエリエラー表示）

因みに結合のみとMAX関数を別々に実行すると表示できることを確認しています。

環境は
WinXP
Mysql5.0.41
php5.2.3
です。
まだまだ勉強中ですのでよろしくお願いいたします。

回答No.1

darter · ベストアンサー

もう少しすっきりした文になるかも知れませんが、とりあえず以下のSQL文でどうでしょう。

select
m.id, m.basyo, m.name, y.jyotai, y.time
from
main_tbl m
left join ( select
s.id, s.jyotai, s.time
from
sub_tbl s,
( select
id, max(time) maxtime
from
sub_tbl
group by id) x
where s.id = x.id and s.time = x.maxtime) y
on m.id = y.id

■考え方
１．sub_tblから、id毎に最大時間のデータを抽出したテーブルを作成し、xとする。
select id, max(time) maxtime from sub_tbl group by id

２．xとsub_tblを等価結合することでsub_tblからid毎に最大時間を持つ行を抽出したテーブルを作成し、yとする。
select s.id, s.jyotai, s.time from sub_tbl s , x where s.id = x.id and s.time = x.maxtime

　※この手順は、最大時間を持つ行のjyotaiを取得するため。

３．main_tblとyを外部結合する。
select m.id, m.basyo, m.name, y.jyotai, y.time from main_tbl m left join y on m.id = y.id

お礼

早速のアドバイスありがとうございます。
x,yに代入するという概念が自分の頭の中にありませんでした。
非常に丁寧な解説で初心者の私でも理解できました。
参考にさせて頂きます。また困った時はご指導よろしくお願いいたします。

回答No.2

yambejp

こんなんでどうです？

SELECT main_tbl.id, basyo,name,jyotai,time
FROM main_tbl
LEFT JOIN (
SELECT id,jyotai,time
FROM sub_tbl
WHERE (id,time) IN (SELECT id,max(time) from sub_tbl group by id)
) as x
ON x.id=main_tbl.id

お礼

アドバイスありがとうございます。
一発で表示できました。
非常に洗練された構文ですね。
まだまだ勉強不足だと痛感しました。
また機会がありましたらご指導の程よろしくお願いいたします。