表の結合の仕方

質問

お世話になります。
このたび今後の業務に必要な為、SQLを勉強することになりました。そこでこのような問題が出たのですがどうも理解できません。

【商品マスタ】
商品コード　商品名　　単価
0001　　　　ボール　　100
0002　　　　バット　　200
0003　　　　グローブ　300

【仕入先マスタ】
仕入先コード　仕入先
001　　　　　　A
002　　　　　　B
003　　　　　　C

【トラン】
購入番号　　商品コード　仕入先コード　数量
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　　　　　 0004　　　　004　　　　　　20

上記の商品、仕入先、トランのテーブルから以下のように出力をするものです。

商品コード、商品名、仕入先コード、仕入先名、数量、単価、金額
0001　ボール　　001　A　　　　5　100　　500
0001　ボール　　002　B　　　　7　100　　700
0001　ボール　　003　C　　　　3　100　　300
0002　バット　　001　A　　　　7　200　　1400
0002　バット　　002　B　　　　8　200　　1600
0002　バット　　003　C　　　　6　200　　1200
0003　グローブ　001　A　　　　10 300　　3000
0003　グローブ　003　C　　　　9　300　　2700
0004　(null)　　004　(null)　20 (null)　(null)

ネットを見てもいまいちピンと来ず行き詰っています。ヒント等ありましたらよろしくお願い致します。

回答No.6

CHRONOS_0

これらのテーブルにはリレ-ションシップを設定していないのでしょうか
設定していれば
マスタにない物をトランに登録しようとした時点で怒られるはずですが

回答No.5

root139

#1 です。
#1 の例1は、実は、PostgreSQL7.3で確認したものです。(テーブル名、カラム名は半角英数にしました)
ちゃんとANSI SQLに準拠しているかは、ちょっと自信ないです。

ちなみに例2(Oracle風)は、実行環境が無いので動作確認してません。
大きな間違いは無いと思いますが。

質問者さんはDBMSを明示されていませんが、このカテゴリ＝「その他(データベース)」だと、Access の可能性が高いですね。
今までの回答で解決されましたでしょうか？

回答No.4

BLUEPIXY

>#3どの
確認しました。
ほんとですね。

？？
最初に試した時、何がいけなかったんだろう？
１つ賢くなりました、ありがとうございます。
質問者を混乱させたならすみませんでした。

回答No.3

fu_u_ka_i

>#2どの

#1の書かれた一般的な(ANSI構文の)SQLは、ACCCESS流の表記にするだけで、
特に問題なく動作しますよ。
（というか、これしきのクエリが処理できないようでは困る）

SELECT
トラン.商品コード,
商品マスタ.商品名,
トラン.仕入先コード,
仕入先マスタ.仕入先,
Sum(トラン.数量) AS 数量,
商品マスタ.単価,
Sum(トラン.数量)*商品マスタ.単価 AS 金額
FROM
(トラン LEFT JOIN 商品マスタ ON トラン.商品コード = 商品マスタ.商品コード)
LEFT JOIN 仕入先マスタ ON トラン.仕入先コード = 仕入先マスタ.仕入先コード
GROUP BY
トラン.商品コード,
商品マスタ.商品名,
トラン.仕入先コード,
仕入先マスタ.仕入先,
商品マスタ.単価
;

なお、ACCESS2002で確認しました。

回答No.2

BLUEPIXY

アクセスで＃１の方の一般的なＳＱＬがうまくいかなかったので、私なりに、やってみました。
アクセスでは、SUMとGROUP BYがうまくいかないので、
最初にトランを商品コードと仕入先コードで数量の和を求めておきます（クエリ：ビュー）

SELECT 商品コード, 仕入先コード, SUM(数量) AS 集約数量
FROM トラン
GROUP BY 商品コード, 仕入先コード;

あとは、この集計済みトランについて表の結合を行います。

SELECT T.商品コード, SY.商品名, T.仕入先コード, SI.仕入先 AS 仕入先名, T.集約数量 AS 数量, SY.単価, (T.集約数量*SY.単価) AS 金額
FROM (数量集約 AS T LEFT JOIN 商品 AS SY ON T.商品コード=SY.商品コード) LEFT JOIN 仕入先 AS SI ON T.仕入先コード=SI.仕入先コード;

段階を分けているだけでやってることは同じです。

回答No.1

root139

条件は、下記の様になるのでしょうか？

1.【トラン】テーブルと【商品マスタ】テーブルの「商品コード」項目が同じものを結合。
　ただし、【商品マスタ】テーブルに該当するレコードが無くても、【トラン】テーブルのレコードは全て出力。
2.【トラン】テーブルと【仕入先マスタ】テーブルの「仕入先コード」項目が同じものを結合。
　ただし、【仕入先マスタ】テーブルに該当するレコードが無くても、【トラン】テーブルのレコードは全て出力。
3.「商品コード」と「仕入先コード」が同一のものは、一つにまとめる。
4.【商品マスタ】の「単価」と【トラン】の「数量」(同一の商品・仕入先の合計)から金額を算出。

テーブル同士の結合は、『JOIN』句、または、『WHERE』句で条件を記述します。
また、一方のテーブルは全てのレコードを出力するため、外部結合をする必要が有ります。
http://www.atmarkit.co.jp/fnetwork/rensai/sql06/sql1.html
http://www.techscore.com/tech/sql/06_02.html

複数のレコードを一つにまとめるには、『GROUP BY』句を使用します。
出力する項目で集約関数を使うもの以外は、全て『GROUP BY』句に記述する必要が有ります。
(DBMSによって、例外あり)
ご質問の場合は、まとめる条件は「トラン.商品コード」「トラン.仕入先コード」だけですが、「商品名」などの他の出力項目も『GROUP BY』句に記述します。
http://www.atmarkit.co.jp/fnetwork/rensai/sql03/sql1.html
http://www.techscore.com/tech/sql/05_08.html

金額を算出するには、集約関数のSUM()を使って「数量」の合計を算出し、「単価」と掛け合わせます。
列名を付けたい場合は、「AS」句を使用すると良いでしょう。
http://www.techscore.com/tech/sql/05_07.html

一般的なSQLでは、下記の様になると思います。

例1-------------------------------------------------------------------------
SELECT
トラン.商品コード,
商品マスタ.商品名,
トラン.仕入先コード,
仕入先マスタ.仕入先名,
SUM(トラン.数量) AS 数量,
商品マスタ.単価,
商品マスタ.単価 * SUM(トラン.数量) AS 金額
FROM トラン
LEFT OUTER JOIN 商品マスタ ON (トラン.商品コード = 商品マスタ.商品コード)
LEFT OUTER JOIN 仕入先マスタ ON (トラン.仕入先コード = 仕入先マスタ.仕入先コード)
GROUP BY
トラン.商品コード,
商品マスタ.商品名,
トラン.仕入先コード,
仕入先マスタ.仕入先名,
商品マスタ.単価
----------------------------------------------------------------------------

例2 Oracle風----------------------------------------------------------------
SELECT
トラン.商品コード,
商品マスタ.商品名,
トラン.仕入先コード,
仕入先マスタ.仕入先名,
SUM(トラン.数量) AS 数量,
商品マスタ.単価,
商品マスタ.単価 * SUM(トラン.数量) AS 金額
FROM トラン, 商品マスタ, 仕入先マスタ
WHERE トラン.商品コード = 商品マスタ.商品コード(+) AND トラン.仕入先コード = 仕入先マスタ.仕入先コード(+)
GROUP BY
トラン.商品コード,
商品マスタ.商品名,
トラン.仕入先コード,
仕入先マスタ.仕入先名,
商品マスタ.単価
----------------------------------------------------------------------------