3つのテーブルから、在庫を導き出したい

Question

初めに

現在、3つのテーブル貸し出し履歴、文書目録、文書台帳から、在庫を導き出そうとしていますが、
うまくできません。

##テーブル

1. 貸し出し履歴(rentalhistory)

カラム説明
・ rental_id ・・・貸し出しID
・ member_id ・・・利用者ID
・ doc_id　・・・文書ID
・ rental_date・・・貸し出し年月日
・ deadline_date・・・返却期日
・ return_date・・・返却年月日

1. 文書目録(doc_resister)

カラム説明
・ doc_id　・・・文書ID
・ doc_isbn・・・isbn番号
・ arrival_date・・・入荷年月日
・ disposal_date・・・廃棄年月日

1. 文書台帳

カラム説明
・ doc_isbn・・・isbn番号
・ doc_name・・・文書名
・ sort_id・・・分類コード
・ author・・・著者
・ publisher・・・出版社
・ release_date・・・出版日

##実現したいこと
isbn番号、文書名、分類コード、著者、出版社、出版日、在庫

を表示させたいです。

##試したこと
在庫は、
本の数-まだ貸し出しされていない冊数=在庫だと思っているのですが、
なかなかうまくいきません。

select l.doc_isbn, l.doc_name, l.sort_id, l.author, l.publisher, l.release_date, count( l.doc_isbn) 
FROM doc_list l FULL JOIN doc_register d ON l.doc_isbn = d.doc_isbn FULL JOIN rental r ON d.doc_id = r.doc_id 
WHERE sort_id = ? and (rental_id is null or return_date is not null ) 
GROUP BY l.doc_isbn, l.doc_name

javaからsql文を実行しているので、プレースホルダーが1つありますが、ここには分類コード入れています。

実行結果は、
在庫ではなく、
本の数全体が出てきてしまいます。

Comments

[Orlofsky]

質問に、そのうまくいかないSELECT文も https://teratail.com/help/question-tips#questionTips3-7 の [コード] で追記しては？

[Orlofsky]

質問のテーブル定義とデータはキャプチャではなく CREATE TABLE文とINSERT文で提示できた方が良いです。上記のMarkdownの [コード] を使ってね。

[unity3dbigginer]

追加いたします。
ありがとうございます、

Answer 1

集計を取る際のコツは、最終的な集計の単位にするまでを段階的に区切る事です。

質問の場合必要があるのは、doc_isbnをキーの一部に含むものだけですので、doc_resisterとrentalhistoryを結合したものを対象に集計します。

在庫を求めるには基準となる日が必要ですので現在日(current_date)を使用。
また、期間内に該当するかの判断で、日付型を前提にinfinityを使用してます。
※可能ならテーブルの初期値としてinfinityを設定しておくとNull判断が不要になりますのでお勧め。

select l.*, coalesce(s.stock_num, 0) as stock_num, coalesce(s.rental_num, 0) as rental_num
from  doc_list l
      left join (
        select  doc_isbn
              , sum((
                  current_date 
                     between arrival_date 
                         and coalesce(disposal_date, 'infinity')
                )) as stock_num
              , sum((rental_date is not null and return_date is null)) as rental_num
        from  doc_resister d
              left join rentalhistory r
              on  d.doc_id=r.doc_id
        group by doc_isbn
      ) s
      on    l.doc_isbn=s.doc_isbn

貸出可能数　=　在庫 - 貸出中数
でないかと思いますが明確ではないので、計算はせず、在庫と貸出中数を取得しています。

Comments

[unity3dbigginer]

ありがとうございます。
こちらを入力したところ、確認できました！

Answer 2

FULL JOIN を INNER JOIN にしたらどうなりますか？

Comments

[unity3dbigginer]

INNER JOINにした場合、
正しくは2と出る本が、在庫1となってしまいます。

[Orlofsky]

きちんとSQLの動作確認できるように、
上の方に
>質問のテーブル定義とデータはキャプチャではなく CREATE TABLE文とINSERT文で提示できた方が良いです。上記のMarkdownの [コード] を使ってね。
ってのも書いたんですけど。