SQLを教えてください

Question

下記のような３つのテーブルから商品テーブルに登録されているすべての商品に対する価格設定、受注を表示したいのですが、
どのように考えればよいのかわかりません。

商品テーブル
item lot
大根　L
大根　M
大根　S

価格設定テーブル
item lot price
大根　L　200
大根　M 100

受注テーブル
item lot order
大根　L　5
大根　M 10

表示したい内容
商品テーブルに登録されているすべての商品に対する価格設定、受注

item lot　price order
大根　L 200 5
大根　M 100 10
大根　S 0 0

どのようにすればよいでしょうか

Comments

[m.ts10806]

タイトルには要件を書いてください。 https://teratail.com/help/question-tips#questionTips3-1 >ありがちな失敗としては、「助けてください」「初心者です」という表題をつけてしまうことです。

[Orlofsky]

priceを直接 商品テーブル に持てば 価格設定テーブル は不要では？

Answer 1

受注テーブルは商品につき1件ということはないでしょうから、集計しています。

select itm.item,  itm.lot, prc.price, coalesce(odr.order_sum, 0) 
from  商品テーブル itm
      left join 価格設定テーブル prc
      on    itm.item=prc.item 
        and itm.lot=prc.lot
      left join (
        select item, lot, sum(`order`) as order_sum 
        from 受注テーブル
        group by item, lot
      ) odr
      on    itm.item=odr.item 
        and itm.lot=odr.lot
order by itm.item,  itm.lot

または

select itm.item,  itm.lot, prc.price
     , coalesce((select sum(`order`) from 受注テーブル where item=itm.item  and lot=itm.lot), 0)
from  商品テーブル itm
      left join 価格設定テーブル prc
      on    itm.item=prc.item 
        and itm.lot=prc.lot
order by itm.item,  itm.lot

Answer 2

select item,  lot,　price,  order
from (商品テーブル left join 価格設定テーブル on 商品テーブル.item=価格設定テーブル.item 
and 商品テーブル.lot=価格設定テーブル.lot)
 left join  受注テーブル on 商品テーブル.item=受注テーブル.item 
and 商品テーブル.lot=受注テーブル.lot

で、どうでしょうか？

Answer 3

つLEFT OUTER JOIN

Answer 4

SQLの例はもう出ているので、考え方を説明します。

他のテーブルに値がなくても表示したい、というときには、LEFT (OUTER) JOINを使うのが一般的です。

参考：これでわかった!? LEFT / RIGHT JOIN.

結果のテーブルのイメージができているのであれば、あとは商品テーブルをメインにして
各テーブルと項目を=でつなぐだけ。
注意したいのは、値がない場合、nullと表示されてしまうこと。
ただ、LEFT JOINしただけだと、大根　Sの行はnullと表示されます。
これを関数を使ってnull -> 0に補完してあげます。

関数を使うときはDBの種類によって変わってくるので、ライブラリを調べるといいと思います。
参考：SQL文でNullの置換

Comments

[sazi]

Nullの置換については、COALESCEがSQL標準で、現状では、主だったDBMSは対応しています。（リンク先は古いですね）

[kaputaros]

あぁ、タグを見落としてました。MySQLなんですね。
そして、他のDBでもCOALESCE使えるんですね。
ご指摘ありがとうございます。

でも、DBのバージョンによっては使えない可能性がある、ということですね。
勉強になりました。

[Qoo]

回答ありがとうございます。
リンク先とても参考になりました。
2つのテーブルまではわかりやすかったのですが、３つ以上のテーブルで混乱していました。。
商品テーブルをメインにしてくっつけていけば良いのですね！
COALESCEについても参考になりました！ありがとうございました。