2つのSQLを1つにまとめたい

Question

前提・実現したいこと

商品の発注を行っており、
予測数量（YOSOKU_SURYOU）が0以下の商品と、
現在すでに発注をかけている商品の数量（TOTAL_HATCHU_SURYOU）を比較して
追加で発注が必要なアイテムを出力したいです。

「TOTAL_HATCHU_SURYOU」は現在発注をかけている数量となりますので
マイナスケースになることはありません。

予測数量（YOSOKU_SURYOU）は、
ある一定の時期までに、在庫として残るアイテム数の予測であり、
0またはマイナスの場合は追加発注が必要なアイテムということになります。

追加発注が必要なアイテムの中でも、
現在すでに発注をかけている商品の数量（TOTAL_HATCHU_SURYOU）で
足りるかどうかを確認したいです。

発生している問題・エラーメッセージ

(1)のSQLで出力したITEM_IDと
(2)のSQLで出力したITEM_IDが一致していて

(2)の「YOSOKU_SURYOU」と
(1)の「TOTAL_HATCHU_SURYOU」を足した時に
【0またはマイナスになる】ITEM_IDを出力したいです。

上記を1つのSQLで出力する方法が分からず、
どのように記述すれば良いかご教示いただきたいです。

例）
予測数量（YOSOKU_SURYOU）が-20で、
発注数量（TOTAL_HATCHU_SURYOU）が5の場合は、
発注数量が15個足りないので追加発注が必要
該当のITEM_IDをSELECT結果に出力したい

該当のソースコード

(1)
SELECT
    ITEM_HATCHU.ITEM_ID,
    SUM(HATCHU_SURYOU) as TOTAL_HATCHU_SURYOU
FROM
    ITEM_HATCHU
GROUP BY
    ITEM_HATCHU.ITEM_ID;

(2)
SELECT
    INFO.ITEM_ID
FROM
    ITEM_INFO
        INNER JOIN ITEM_YOSOKU ON
            ITEM_INFO.ITEM_ID = ITEM_YOSOKU.ITEM_ID AND
            ITEM_YOSOKU.YOSOKU_SURYOU <= 0
ORDER BY
    INFO.ITEM_ID;



追記【確認用 CREATE TABLE】------------

CREATE TABLE `ITEM_HATCHU` (
  `ITEM_ID` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'アイテムID',
　`HATCHU_ID` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT '発注ID',
  `HATCHU_SURYOU` int(8) NOT NULL COMMENT '発注数量',
  PRIMARY KEY (`HATCHU_ID`),
  KEY `ITEM_ID` (`ITEM_ID`)
)

CREATE TABLE `ITEM_INFO` (
  `ITEM_ID` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'アイテムID',
  `ITEM_NAME` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'アイテムID',
  PRIMARY KEY (`ITEM_ID`)
)

CREATE TABLE `ITEM_YOSOKU` (
  `ITEM_ID` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'アイテムID',
  `YOSOKU_SURYOU ` int(8) NOT NULL COMMENT '予測数量',
  PRIMARY KEY (`ITEM_ID`)
) 

追記【確認用 INSERT文】------------

INSERT INTO `ITEM_YOSOKU` (`ITEM_ID`, `YOSOKU_SURYOU`) VALUES ('001','-20');
INSERT INTO `ITEM_YOSOKU` (`ITEM_ID`, `YOSOKU_SURYOU`) VALUES ('002','-20');
INSERT INTO `ITEM_YOSOKU` (`ITEM_ID`, `YOSOKU_SURYOU`) VALUES ('003','-20');
INSERT INTO `ITEM_YOSOKU` (`ITEM_ID`, `YOSOKU_SURYOU`) VALUES ('004','-20');
INSERT INTO `ITEM_YOSOKU` (`ITEM_ID`, `YOSOKU_SURYOU`) VALUES ('005','-20');

INSERT INTO `ITEM_INFO` (`ITEM_ID`, `ITEM_NAME`) VALUES ('001','アイテムA');
INSERT INTO `ITEM_INFO` (`ITEM_ID`, `ITEM_NAME`) VALUES ('002','アイテムB');
INSERT INTO `ITEM_INFO` (`ITEM_ID`, `ITEM_NAME`) VALUES ('003','アイテムC');
INSERT INTO `ITEM_INFO` (`ITEM_ID`, `ITEM_NAME`) VALUES ('004','アイテムD');
INSERT INTO `ITEM_INFO` (`ITEM_ID`, `ITEM_NAME`) VALUES ('005','アイテムE');

INSERT INTO `ITEM_HATCHU` (`ITEM_ID`, `HATCHU_ID`, `HATCHU_SURYOU` ) VALUES ('001','HA001','30');
INSERT INTO `ITEM_HATCHU` (`ITEM_ID`, `HATCHU_ID`, `HATCHU_SURYOU` ) VALUES ('002','HA002','30');
INSERT INTO `ITEM_HATCHU` (`ITEM_ID`, `HATCHU_ID`, `HATCHU_SURYOU` ) VALUES ('003','HA003','30');
INSERT INTO `ITEM_HATCHU` (`ITEM_ID`, `HATCHU_ID`, `HATCHU_SURYOU` ) VALUES ('004','HA004','30');
INSERT INTO `ITEM_HATCHU` (`ITEM_ID`, `HATCHU_ID`, `HATCHU_SURYOU` ) VALUES ('005','HA005','5');

→ITEM_ID「005」は、YOSOKU_SURYOUが「-20」に対して、HATCHU_SURYOUが「5」なので
 追加発注が必要＝SELECT結果にITEM_IDを出力したい

Comments

[m.ts10806]

テーブル定義をCREATE TABLEで提示してください。
あとMySQLのバージョンは幾つでしょうか。

[Orlofsky]

質問にテーブル定義をCREATE TABLE文で、テーブル中のデータをINSERT文で、現行のSEECT結果と希望するSELECT結果を https://teratail.com/help/question-tips#questionTips3-7 の [コード]で追記しては？

[Orlofsky]

ネーミングはローマ字表はヘボン式を使われては？
https://www.ezairyu.mofa.go.jp/passport/hebon.html

[sazi]

>(2)の「YOSOKU_SURYOU」が
>(1)の「TOTAL_HATYUU_SURYOU」よりも【少ない】
>ITEM_IDを出力したいです。

(2)では「YOSOKU_SURYOU <= 0」となっていますので、それに対する比較なら「TOTAL_HATYUU_SURYOU」がマイナスケースがあるという事ですか？
「TOTAL_HATYUU_SURYOU」にマイナスが無いなら、全てが対象になるように思うのですが。

[退会済みユーザー]

m.ts10806様
コメントありがとうございます。
質問内容を訂正させていただきましたので
よろしければご確認いただけますと幸いです。
また、バージョンは6.3になります。

[退会済みユーザー]

sazi様
コメントありがとうございます。
説明が足らず、申し訳ございません。

「TOTAL_HATCHU_SURYOU」は現在発注をかけている数量となりますので
マイナスケースになることはありません。

予測数量（YOSOKU_SURYOU）は、
ある一定の時期までに、在庫として残るアイテム数の予測であり、
0またはマイナスの場合は追加発注が必要なアイテムということになります。

追加発注が必要なアイテムの中でも、
現在すでに発注をかけている商品の数量（TOTAL_HATCHU_SURYOU）で
足りるかどうかを確認したいので
(2)の「YOSOKU_SURYOU」と
(1)の「TOTAL_HATCHU_SURYOU」を足した時に
【0またはマイナスになる】ITEM_IDを出力したいです。
※質問も訂正させていただきました。

例）
予測数量（YOSOKU_SURYOU）が-20で、
発注数量（TOTAL_HATCHU_SURYOU）が5の場合は、
発注数量が15個足りないので追加発注が必要
該当のITEM_IDをSELECT結果に出力したい

[退会済みユーザー]

Orlofsky様
コメントありがとうございます。
質問内容を訂正させていただきましたので
よろしければご確認いただけますと幸いです。

Answer 1

(1)のSQLで出力したITEM_IDと
(2)のSQLで出力したITEM_IDが一致していて
(2)の「YOSOKU_SURYOU」と
(1)の「TOTAL_HATCHU_SURYOU」を足した時に
【0またはマイナスになる】ITEM_IDを出力したいです。

このような仕様であればおおよそ以下のようなクエリで実現できるかと思います。

SELECT 
  A.ITEM_ID,
  A.TOTAL_HATCHU_SURYOU,
  B.YOSOKU_SURYOU,
  C.ITEM_NAME
FROM (
  SELECT
    ITEM_ID,
    SUM(HATCHU_SURYOU) AS TOTAL_HATCHU_SURYOU
  FROM ITEM_HATCHU
  GROUP BY ITEM_ID
) AS A
JOIN ITEM_YOSOKU AS B ON A.ITEM_ID = B.ITEM_ID
JOIN ITEM_INFO AS C ON A.ITEM_ID = C.ITEM_ID
WHERE A.TOTAL_HATCHU_SURYOU + B.YOSOKU_SURYOU <= 0

Comments

[退会済みユーザー]

neko_the_shadow様
希望する結果を出力することができました！
丁寧にご教示いただきありがとうございました。
引き続き勉強してまいります。

Answer 2

HATCHU_SURYOU > 0の前提なら、集計しなくとも

SELECT distinct ITEM_ID
FROM ITEM_HATCHU
where ITEM_ID in (
       SELECT INFO.ITEM_ID
       FROM ITEM_INFO
            INNER JOIN ITEM_YOSOKU 
            ON     ITEM_INFO.ITEM_ID = ITEM_YOSOKU.ITEM_ID 
              AND  ITEM_YOSOKU.YOSOKU_SURYOU <= 0
      )

とか、

SELECT INFO.ITEM_ID
FROM  ITEM_INFO
      INNER JOIN ITEM_YOSOKU 
      ON     ITEM_INFO.ITEM_ID = ITEM_YOSOKU.ITEM_ID 
        AND  ITEM_YOSOKU.YOSOKU_SURYOU <= 0
where exists(
        select 1 from FROM ITEM_HATCHU
        where ITEM_ID=ITEM_INFO.ITEM_ID
      )

とかで良いのではないでしょうか。
※後者の方がコストは低いと思われます。

(1)の「TOTAL_HATCHU_SURYOU」を足した時に
【0またはマイナスになる】ITEM_IDを出力したいです。

この条件を見落としていました。

SELECT INFO.ITEM_ID
FROM  ITEM_INFO
      INNER JOIN ITEM_YOSOKU 
      ON     ITEM_INFO.ITEM_ID = ITEM_YOSOKU.ITEM_ID 
        AND  ITEM_YOSOKU.YOSOKU_SURYOU <= 0
where exists(
        select 1 from FROM ITEM_HATCHU
        where ITEM_ID=ITEM_YOSOKU.ITEM_ID
        group by ITEM_ID
        having sum(HATCHU_SURYOU) <= ITEM_YOSOKU.YOSOKU_SURYOU
      )

※検証していないので、エラーになったらごめんなさい。

Comments

[退会済みユーザー]

sazi様
EXISTS句は初見でしたが、このように使用するのですね。
迅速且つ丁寧にご教示いただき誠にありがとうございました。
質問方法についても、今後参考にさせていただきます。