GROUP BYと副照会の共存について

Question

###GROUP BYと副照会の共存について
学校の課題でわからないところがあり、投稿させていただきました。
特に副照会とGROUP BYの共存の仕方についてです。

まず、課題というのは「大阪商会が発注した商品と同じ商品を発注したことのある顧客の顧客名とその住所のリストを返すSQLコマンドを作成せよ。ただし、同じ企業名が重複して表示されないようにすること」というものです。(課題で使う、データベースについては下に画像として張っておきます。少し見にくいかもしれませんが、)

僕はこの課題を攻略するため、
① 顧客表と受注明細表を結合
↓
②顧客表の大阪商会という行を選ぶ。
↓
③そこから受注明細表の商品番号(つまり、大阪商会が買った商品番号)を取得
↓
④副照会を使い、③の商品番号と、受注明細表の商品番号をつきあわせる(大阪商会の買った商品を買った顧客番号を得るため)
↓
⑤かぶっている顧客名をGROUP BYでまとめる
↓
⑥顧客表と受注明細表を結合
↓
⑦④の顧客番号を見て、顧客表から顧客名を割り出す。
という道筋を立てました。

しかし、題目にも書いた通り⑤の部分、「かぶっている顧客名をGROUP BYでまとめる」ことができませんでした。
というのも、GROUP BYをどこに書いたとしてもエラーとなってしまうからです。
以下は⑤を抜いたコードです。

SELECT KOKYAKUMEI　　　　　　　　　　７
FROM KOKYAKU X ,JMEISAI Y
WHERE X.KOKYAKUNO=Y.KOKYAKUNO　　　6
AND Y.SHOHINNO IN(　　　　　　　　　4
SELECT SHOHINNO　　　　　　　　　　 3
FROM KOKYAKU X,JMEISAI Y
WHERE X.KOKYAKUNO=Y.KOKYAKUNO　　　1
AND X.KOKYAKUMEI='大阪商会'        2
)

・・・
GROUP BY KOKYAKUMEI　　　　　　　　5
・・・どこに書いてもエラー　　　　　　　　

このコードの結果は狙い通り、上手くでました。

東京商事
東京商事
大阪商会
大阪商会
西中国商店

あとはこれをまとめればいいだけなのですが、その一線がどうしても超えられません。
つまり、GROUP BYと副照会の共存についてできません。
私の考えた方のどこが間違っているのか・・・？
どうかご教授おねがします！！！

Comments

[yambejp]

create tableとinsert intoでテーブルサンプルを文字起こししてください

[hood]

ご指摘ありがとうございます。やはり、文字起こしは礼儀ですよね・・・今回は回答がついたのですが、また今度質問する機会があれば、そのようにしたいと思います・・・申し訳ありませんでした・・・

Answer 1

シンプルに。

SELECT DISTINCT
    KOKYAKUMEI
FROM KOKYAKU X ,JMEISAI Y
WHERE X.KOKYAKUNO = Y.KOKYAKUNO
AND X.KOKYAKUMEI = '大阪商会'

or

SELECT DISTINCT
    KOKYAKUMEI
FROM KOKYAKU X ,JMEISAI Y
WHERE X.KOKYAKUNO = Y.KOKYAKUNO
AND X.KOKYAKUMEI IN('東京商事','大阪商会', '西中国商店')

じゃダメなの？

同じSQLでもデータベースによって方言が大きいですから、どのデータベースを使うのかを質問のタグで明示したり、バージョンも明記した方が適切なコメントが付き易いです。 SQLの観点から Oracle Database, PostgreSQL, MySQL の特徴を整理しよう！

Comments

[sazi]

注文された商品を経由してないから、駄目でしょう。

[Orlofsky]

受注明細表を使っているから大丈夫かと思いました。

[hood]

遅くなって申し訳ありません。回答ありがとうございます。
やはり、大阪商会の商品番号から参照させたいので、saziさんに教わった方法をとりたいと思います・・・
方言が大きいことなど、教えてくださり、ありがとうございました・・・

Answer 2

「大阪商会が発注した商品と同じ商品を発注したことのある顧客の顧客名とその住所のリストを返すSQLコマンドを作成せよ。ただし、同じ企業名が重複して表示されないようにすること」

求めたい結果は、「顧客名とその住所のリスト」
その結果に対する条件は、「大阪商会が発注した商品と同じ商品を発注したことのある顧客」
「ただし、同じ企業名が重複して表示されないようにすること」については、「顧客名とその住所のリスト」という性質上あり得ないことなので、課題としての注釈ですね。

テーブルの構成を見ると、顧客表に対して「顧客番号」条件にする必要があることは分かる。
じゃあ、条件として「大阪商会が発注した商品と同じ商品を発注したことのある顧客」の問い合わせ結果を条件にすればいい事に。
大阪照会が注文した商品を求めるには顧客番号と商品番号が分かればいいので、受注明細表で事足りる。
その商品を注文している顧客は逆のパターンで受注明細表で求められる。

SELECT * FROM KOKYAKU 
WHERE KOKYAKUNO in (
        select KOKYAKUNO from JMEISAI
        where SHOHINNO in (select SHOHINNO from JMEISAI where KOKYAKUNO='D010')
      )

結合すると件数が増えるけど、結合して取得する項目ではないので、単に条件としてin を使用すれば、group by は不要ですね。
リストには大阪商会も含まれるので、大阪商会以外のリストなら条件を追加。

Comments

[hood]

回答ありがとうございます。
ご教授いただいたコード、試してみたら上手くいきました。

なるほど・・・受注明細表で大阪商会を絞り込めばよかったのですね・・・
端的かつ論理的な答え・・・脱帽の二文字です。
本当にありがとうございました！