SQL文で表すには

質問

以下の表を用いてSQL文を作成しようと思ってるんですが、途中までしか思いつかないので、どなたかご教授のほうよろしくお願いします。

shohin shiire
商品番号　商品名　単価　　　仕入番号　仕入先名
　0001 みかん 100　　　　　001 　Ａ
0002 りんご　200　　　　　002 Ｂ
0003 ぶどう　300　　　　　003　　　Ｃ

tran
購入番号　商品番号　仕入番号　数量
1 0001 001 1
2 0001 002 2
3 0001 003 3
4 0001 001 4
5 0001 002 5
6 0002 003 6
7 0002 001 7
8 0002 002 8
9 0003 003 9
10 0003 001 10
11 null 0004 20

これを以下のように出力するためにはどのようなSQL文を書けばよいですか？

商品番号　商品名　仕入番号　仕入先名　数量　単価　金額
　0001 みかん 001　　　 Ａ　　　5 100 500
　0001 みかん 002　　　 Ｂ　　　7 100 700
　0001 みかん 003　　　 Ｃ　　　3 100 300
　0002 りんご 001　　　 Ａ　　　7 200 1400
　0002 りんご 002　　　 Ｂ　　　8 200 1600
　0002 りんご 003　　　 Ｃ　　　6 200 1200
　0003 ぶどう 001　　　 Ａ　　 10 300 3000
　0003 ぶどう 003　　　 Ｂ　　　9 300 2700
　0004 null 004　　　null　 　20 null null

ここまでは正しいと思うのですが・・・
select shohin.商品番号, shohin.商品名, shiire.仕入番号,
　　　　tran.数量, shohin.単価, tran.数量 * shohin.単価 as 金額

よろしくお願いします。

回答No.2

xKENx · ベストアンサー

期待する結果の8行目の仕入先名はＢではなくＣ、9行目の商品番号は0004ではな
くnullですね？

文章で書くと、以下のような感じでしょうか。
tran表を商品番号、仕入番号ごとに数量を集計。商品番号をキーとしてshohin表
からから商品名と単価、仕入番号をキーとしてshiire表から仕入先名を取得。
集計済みの数量×単価を金額列として表示。

1回のSELECT発行で結果を求めるならtran表集計部分をサブクエリーにする必要
がありますね。以下のようにしてはどうでしょうか。

SELECT tran.商品番号
, shohin.商品名
, tran.仕入番号
, shiire.仕入先名
, tran.数量合計
, shohin.単価
, tran.数量合計 * shohin.単価 as 金額
FROM ((SELECT tran.商品番号
, tran.仕入番号
, SUM( tran.数量 ) as 数量合計
FROM tran
GROUP BY tran.商品番号, tran.仕入番号 ) tran
LEFT OUTER JOIN shohin ON tran.商品番号 = shohin.商品番号)
LEFT OUTER JOIN shiire ON tran.仕入番号 = shiire.仕入番号

お礼

表が見にくいのにもかかわらず応えて頂きありがとうございます。

>FROM ((SELECT tran.商品番号
>, tran.仕入番号
>, SUM( tran.数量 ) as 数量合計

FROMの中で作成できるんですね。勉強になりました。

参考にさせて頂いた結果うまく表示させることができました。
ありがとうございました。

回答No.1

g_express999

SELECT
shohin.商品番号,
shohin.商品名,
shiire.仕入番号,
shiire.仕入先名,
tran.数量,
shohin.単価,
tran.数量*shohin.単価 as 金額
FROM
shohin,
shiire,
tran
WHERE tran.商品番号=shohin.商品番号(+)
AND tran.仕入番号=shiire.仕入番号(+)

でどうですか？

お礼

表がすごく見にくいのに回答して頂きありがとうございます。
表だけ作成するにはこれでいいですよね＾＾
ありがとうございます。