3つのテーブルを結合した場合のWHERE

質問

MySQLで3つのテーブルを結合した場合のWHEREがよくわかりません。

まず、下のような2つのテーブルがあるとします。

【テーブルaa】
ID | Name
--------------
1 | x
2 | y
3 | z

【テーブルbb】
ID | hizuke | category
---------------------
1 | stamp1 | a
1 | stamp2 | b
1 | stamp3 | c
2 | stamp1 | a
2 | stamp2 | d
3 | stamp1 | c

この2つのテーブルを結合して、同じIDでhizukeが最大値のデータを抽出するクエリは、
http://okwave.jp/qa/q6918385.htmlを参考にして、

SELECT aa.Name, bb.category FROM bb
INNER JOIN aa ON aa.ID = bb.ID
where (bb.ID, bb.hizuke) in (SELECT bb.ID, max(bb.hizuke) FROM bb group by bb.ID ) ;
でできました。

しかし、もう1つテーブルを結合すると期待した結果が得られなくなってしまいました。

【テーブルcc】
category | choice
---------------------
a | ss
b | tt
c | uu

というテーブルがあって、上のクエリで得られた bb.categoryと cc.categoryをリンクして、choiceを取得しようとしたのですがうまくいきません。

SELECT aa.Name, bb.hizuke, bb.category, cc.choice FROM bb
INNER JOIN cc ON bb. category = cc. category
INNER JOIN aa ON aa.ID = bb.ID
where (bb.ID, bb.category, bb.hizuke) in (SELECT bb.ID, bb.category, max(bb.hizuke) FROM bb group by bb.ID );
というクエリでは、ダメなようです。

INNER JOINを入れ子にしたり、いろいろと試したのですがどうにもうまくいきません。
どうも私の力では解決困難なようです。どなたかヘルプを。

回答No.2

maiko0318

ccが複数件あっても一件にすればいいのかな？

NAME HIZUKE CATEGORY CHOICE
---- ------ -------- ------
x stamp3 c uu
z stamp1 c uu

2 レコードが選択されました。

select aa.Name,bb.hizuke,bb.category,cc.choice from aa,bb,cc where bb.category=cc.category and (cc.category,cc.choice) in (select cc.category,max(cc.choice) from cc group by cc.category) and aa.ID=bb.ID and (bb.ID,bb.hizuke) in (select bb.ID,max(bb.hizuke) from bb group by bb.ID)

お礼

maiko0318さん、非常に参考になるSQLをどうもありがとうございます。

しかし、教えて頂いたSQLを実行すると、私の環境ではなぜか

Name hizuke category choice
---------------------------
x stamp3 c uu
z stamp1 c uu
x stamp3 c uu
z stamp1 c uu

となってしまいます。これこそが最初から、悩んでいた現象です。
2つの表の結合ではきちんと抽出できたのにテーブルccを結合させると、このように必要ないものまでが表示されてしまいます。
bb.categoryに対してテーブルccからchoiceを1つだけ取ってくればいいのですが・・・

ということで、最初のSQLにdinstinctを追加したら、一応望んだ結果が抽出できました。

select distinct aa.Name,bb.hizuke,bb.category,cc.choice from bb
inner join cc on bb.category=cc.category
inner join aa on aa.ID=bb.ID
where (bb.ID,bb.hizuke) in(select bb.ID,max(bb.hizuke) from bb group by bb.ID)

ただし、論理的に理解しておらず、結果がたまたまよかったという感じなのでかなり不安です。
こんなSQLでいいんでしょうかねぇ？

回答No.1

maiko0318

NAME HIZUKE CATEGORY CHOICE
---- ------ -------- ------
x stamp3 c uu
z stamp1 c uu

2 レコードが選択されました。

db2 select aa.Name,bb.hizuke,bb.category,cc.choice from bb inner join cc on bb.category=cc.category inner join aa on aa.ID=bb.ID where (bb.ID,bb.hizuke) in(select bb.ID,max(bb.hizuke) from bb group by bb.ID)

これでいいですか？

お礼

maiko0318さん、どうもありがとうございます。
でも書いて頂いたクエリは、私の質問欄のダメなようですと書いたクエリと同じですよね。
しかし、確かに私が書いた質問内容ではこのクエリできちんと抽出できます。

実際の複雑なデータベースでおこなうと変な結果になるのに、なぜだろう？と調べてみたところ、
テーブルccを簡略化しすぎたのが原因のようです。申し訳ありませんでした。
実際のテーブルccは

category | choice
---------------------
a | ss
a | ss
b | tt
b | tt
c | uu
c | uu

というように同じものが複数存在しているのです。もちろん、他にもフィールドがあります。
このようになっていると、思ったような結果は得られません。
この場合は、どのようなSQLにしたらよろしいでしょうか？