複数のtableから条件に一致するデータを取り出す方法

質問

複数のtableから条件に一致するデータを取り出す方法

2つのテーブルから条件に一致するレコードだけを取り出したいです。
MySQLのバージョンは5.0.77です。
-------------------------------------------------------------------
「テーブル1」

[itemNo] int型 auto_increment
[itemName] varchar型

itemNo　itemName　　　　flag
1　　　　apple　　　　　0
2　　　　orange　　　　　1
3　　　　grape　　　　　0
4　　　　strawberry　　　1
5　　　　melon　　　　　1

「テーブル2」
[logNo] int型 auto_increment
[itemNo] int型
[date] date型
[itemLog] int型

logNo　itemNo　date　　　　itemlog
1　　　4　　　2010/07/28　　1
2　　　2　　　2010/07/28　　2
3　　　2　　　2010/08/03　　1
4　　　1　　　2010/08/04　　1
5　　　4　　　2010/08/05　　2

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

・テーブル1の「itemNo」とテーブル2の「itemNo」は同じ番号が入ります。
・テーブル2は1商品に対する履歴用のテーブルなので、同じ「itemNo」が複数存在します。

「検索条件」
1.テーブル1の[itemNo]とテーブル2の[itemNo]が一致
2.テーブル1の[flag]が１のデータのみ表示
2,テーブル2に同一の[itemNo]が複数あった場合、[logNo]が新しい最新のものを取り出す
3.テーブル2に[itemNo]が存在しなくても、flagが1なら取り出す

SELECT * FROM `テーブル1` LEFTJOIN `テーブル2` ON (テーブル1.itemNo = テーブル2.itemNo) WHERE `flag` == 1 ORDER BY テーブル1.itemNo DESC;

「実行結果」

itemNo　itemName　　　flag　logNo　itemNo　date　　　　itemlog
2　　　orange　　　　1　　　2　　　2　　　2010/07/28　2
2　　　orange　　　　1　　　3　　　2　　　2010/08/03　1
4　　　strawberry　　1　　　1　　　4　　　2010/07/28　1
4　　　strawberry　　1　　　5　　　5　　　2010/08/05　2
5　　　melon　　　　　1　　　

「求める実行結果」
itemNo　itemName　　　flag　logNo　itemNo　date　　　　itemlog
2　　　orange　　　　1　　　3　　　2　　　2010/08/03　1
4　　　strawberry　　1　　　5　　　5　　　2010/08/05　2
5　　　melon　　　　　1　　　

ご存知の方がいましたら、教えていただけると幸いです。
何卒よろしくお願い致します。

回答No.3

yambejp

#2、言い忘れ・・・

dateは予約語なので勝手にdatexにフィールド名を変えています

回答No.2

yambejp

求める実行結果データ行の２行目の真ん中のitemNoは5ではなくて4じゃない？
で、こんな感じで

select t1.itemNo,itemName,flag,t2.logNo,t2.itemNo,datex,itemlog
from テーブル2 as t2
inner join( select itemNo,max(logNo) as max_logNo from テーブル2 group by itemNo) AS sub
on t2.itemNo=sub.itemNo and logNo= max_logNo
right join テーブル1 as t1
on t1.itemNo=t2.itemNo
where flag=1

回答No.1

noname#245936

…DBMSが違いますが、SQL Serverでこさえてみました。
（なお、貴方ご提示のSQLはOrder Byの順序がヘンです。）

基本はこのSQLができればGoodです。

select
Table1.itemNo,
Max(Table2.logNo) as logno
From Table1
left outer join Table2
On Table1.itemNo = Table2.itemNo
Where flag = 1
group by Table1.itemNo

求める実行結果の主キーが取れます。

itemNo　 LogNo
2　　　　　3
4　　　　　5
5　　　　　NULL

こいつをもとにSQL一撃で出すならこんな書き方ができるでしょう。
Select
KeyTable.itemNo,
KeyTable.logNo ,
Table1.*,
Table2.*
From
(select
Table1.itemNo,
Max(Table2.logNo) as logno
From Table1
left outer join Table2
On Table1.itemNo = Table2.itemNo
Where flag = 1
group by Table1.itemNo
)
as keyTable
Left Outer Join Table1
On keyTable.itemNo = Table1.itemNo
Left outer join Table2
On keyTable.itemNo = Table2.itemNo
AND keyTable.logno = Table2.logNo

結果
2　　3　　2　　orange　　1　　3　　2　　2010-08-03　　1
4　　5　　4　　strawb　　1　　5　　4　　2010-08-05　　2
5　　NULL　　5　　melon　　1　　NULL　　NULL　　NULL　　NULL

あとはOrderByの追加と、要らない検証用の列定義を最初のSELECT句から
削れば完成です。

データ量や、各抽出フィルタ条件の分布によっては、このSQLは遅いので
フィルタ後のDB同士を掛け合わせる形がいいと思います。

いかがでしょうか