【穴埋めお題】恐竜の新説

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

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
です。
まだまだ勉強中ですのでよろしくお願いいたします。

A 回答 (2件)

もう少しすっきりした文になるかも知れませんが、とりあえず以下の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


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


2.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を取得するため。


3.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
    • good
    • 7
この回答へのお礼

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

お礼日時:2007/08/30 09:16

こんなんでどうです?



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
    • good
    • 7
この回答へのお礼

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

お礼日時:2007/08/30 09:22

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています


おすすめ情報

このQ&Aを見た人がよく見るQ&A