2つの関連するテーブルの並び替え

質問

mysql5.1.33を使用しています。

テーブル「tbl1」
id int(11)

テーブル「tbl2」
id int(11)
num1 int(11)

テーブル「tbl1_tbl2」
id int(11)
tbl1_id int(11) tbl1の外部キー
tbl2_id int(11) tbl2の外部キー

テーブル「tbl1」
+------+
|　　id　　|
+------+
|　　1　　|
+------+
|　　2　　|
+------+
|　　3　　|
+------+
|　　4　　|
+------+

テーブル「tbl2」
+------+------+
|　　id　　| num　|
+------+------+
|　　1　　|　5　　|
+------+------+
|　　2　　|　10　　|
+------+------+
|　　3　　|　50　　|
+------+------+
|　　4　　|　100　|
+------+------+

テーブル「tbl1_tbl2」
+------+-------+-------+
|　　id　　| tbl1_id　|　tbl2_id　|
+------+-------+-------+
|　　1　　|　1　　　　|　2　　　|
+------+-------+------+
|　　2　　|　1　　　　|　3　　　|
+------+-------+------+
|　　3　　|　2　　　　|　1　　　|
+------+-------+------+
|　　4　　|　4　　　　|　4　　　|
+------+-------+------+

というテーブル構成で、tbl1の所有するtbl2のnum1の平均値によって降順にソートしたいのですが
どのようなSQL文になるのでしょうか？
例えばこの場合では、
tbl1のid「1」は、（tbl1_tbl2のid1「1」のtbl2_idのnum（10）＋　tbl1_tbl2のid1「2」のtbl2_idのnum（50））÷2＝30
tbl1のid「2」は、（tbl1_tbl2のid1「3」のtbl2_idのnum（5））÷1＝5
tbl1のid「3」は、tbl1_tbl2にないので＝0
tbl1のid「4」は、（tbl1_tbl2のid1「4」のtbl2_idのnum（100））÷1＝100
なので
テーブル「tbl1」は
+------+
|　　id　　|
+------+
|　　4　　|
+------+
|　　1　　|
+------+
|　　2　　|
+------+
|　　3　　|
+------+
に並べ変えられた結果が得たいです。

回答No.1

yambejp · ベストアンサー

こんな感じでは？

SELECT id
FROM tbl1
LEFT JOIN (
select tbl1_id,avg(tbl2.num) as average
FROM tbl1_tbl2
INNER JOIN tbl2 ON tbl1_tbl2.tbl2_id=tbl2.id
GROUP BY tbl1_id
) AS SUB ON tbl1.id=SUB.tbl1_id
ORDER BY average DESC

お礼

ご回答ありがとうございます。
そのやり方で理想とする結果が得られました。ありがとうございます。