SQLの出力方法について

前提・実現したいこと

在庫管理システムを練習として作成しています。
在庫管理を行っているDBにて
各在庫に紐付いているアイテム（ITEM_ID）を出力したいです。

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

以下のsqlで出力したZAIKO_IDと紐付いているITEM_IDを
S_INFO.ZAIKO_IDと同じ行にすべて出力したいのですが、
仕様が複雑なため、良い出力方法が思いつかず、ご教授いただけますと幸いです。

SELECT
  S_INFO.ZAIKO_ID
FROM
  ZAIKO_INFO AS S_INFO
    INNER JOIN ZAIKO_CHECK AS S_CHECK
    ON S_INFO.ZAIKO_ID = S_CHECK.ZAIKO_ID
    AND S_CHECK.SAKJYO_FLG = 0
WHERE
  S_INFO.SAKJYO_FLG = 0;

▼テーブルの仕様は以下の通りです。

【ITEM_INFO】の各ITEM_IDには、【ZAIKO_INFO】のZAIKO_IDが紐付いていて、
ZAIKO_IDが１つ紐付く場合は、【ITEM_INFO】のZAIKO_IDに紐付けて、
ZAIKO_IDが複数紐付く場合は、【ITEM_INFO】のZAIKO_ID_GROUPまたはITEM_ID_GROUPにて
グループとして紐付けられます。
ZAIKO_IDが１つも紐付かない場合は、すべて空となります。

【ZAIKO_INFO_GROUP】のZAIKO_ID_GROUPは
【ZAIKO_INFO】のZAIKO_IDをグループ化したもので、
【ITEM_INFO_GROUP】のITEM_ID_GROUPは
【ITEM_INFO】のITEM_IDをグループ化したものです。
※ZAIKO_IDが複数紐付く場合、アイテムによって、
ZAIKO_ID_GROUPまたはITEM_ID_GROUPのどちらが紐付くかが異なります。

【確認用 CREATE文】
CREATE TABLE `ITEM_INFO` (
  `ITEM_ID` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'アイテムID',
  `ITEM_NAME` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'アイテム名',
  `ZAIKO_ID_GROUP` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT '在庫IDグループ',
  `ITEM_ID_GROUP` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'アイテムグループ',
  `ZAIKO_ID` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT '在庫ID',
  `SAKJYO_FLG` tinyint(1) NOT NULL,
  PRIMARY KEY (`ITEM_ID`)
) 

CREATE TABLE `ZAIKO_INFO` (
  `ZAIKO_ID` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT '在庫ID',
  `ITEM_NAME` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT '在庫名',
  `SAKJYO_FLG` tinyint(1) NOT NULL,
  PRIMARY KEY (`ZAIKO_ID`)
) 

CREATE TABLE `ITEM_INFO_GROUP` (
  `ITEM_ID_GROUP` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'アイテムIDグループ',
  `ITEM_ID` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'アイテムID',
  `SAKJYO_FLG` tinyint(1) NOT NULL,
  PRIMARY KEY (`ITEM_ID_GROUP`,`ITEM_ID`)
)

CREATE TABLE `ZAIKO_INFO_GROUP` (
  `ZAIKO_ID_GROUP` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT '在庫IDグループ',
  `ZAIKO_ID` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT '在庫ID',
  `SAKJYO_FLG` tinyint(1) NOT NULL,
  PRIMARY KEY (`ZAIKO_ID_GROUP`,`ZAIKO_ID`)
)

CREATE TABLE `ZAIKO_CHECK` (
  `ZAIKO_ID` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT '在庫ID',
  `SAKJYO_FLG` tinyint(1) NOT NULL,
  PRIMARY KEY (`ZAIKO_ID`)
)

【確認用 INSERT文】
INSERT INTO `ITEM_INFO` (`ITEM_ID`, `ITEM_NAME`, `ZAIKO_ID_GROUP`, `ITEM_ID_GROUP`, `ZAIKO_ID`, `SAKJYO_FLG`) VALUES ('I001','アイテムA','','','Z002','0');
INSERT INTO `ITEM_INFO` (`ITEM_ID`, `ITEM_NAME`, `ZAIKO_ID_GROUP`, `ITEM_ID_GROUP`, `ZAIKO_ID`, `SAKJYO_FLG`) VALUES ('I002','アイテムB','ZIG003','','','0');
INSERT INTO `ITEM_INFO` (`ITEM_ID`, `ITEM_NAME`, `ZAIKO_ID_GROUP`, `ITEM_ID_GROUP`, `ZAIKO_ID`, `SAKJYO_FLG`) VALUES ('I003','アイテムC','','IIG003','','0');
INSERT INTO `ITEM_INFO` (`ITEM_ID`, `ITEM_NAME`, `ZAIKO_ID_GROUP`, `ITEM_ID_GROUP`, `ZAIKO_ID`, `SAKJYO_FLG`) VALUES ('I004','アイテムD','','','','0');
INSERT INTO `ITEM_INFO` (`ITEM_ID`, `ITEM_NAME`, `ZAIKO_ID_GROUP`, `ITEM_ID_GROUP`, `ZAIKO_ID`, `SAKJYO_FLG`) VALUES ('I005','アイテムE','','','','0');
INSERT INTO `ITEM_INFO` (`ITEM_ID`, `ITEM_NAME`, `ZAIKO_ID_GROUP`, `ITEM_ID_GROUP`, `ZAIKO_ID`, `SAKJYO_FLG`) VALUES ('I006','アイテムF','ZIG001','','','0');

INSERT INTO `ZAIKO_INFO` (`ZAIKO_ID`, `ITEM_NAME`, `SAKJYO_FLG` ) VALUES ('Z001','在庫A','0');
INSERT INTO `ZAIKO_INFO` (`ZAIKO_ID`, `ITEM_NAME`, `SAKJYO_FLG` ) VALUES ('Z002','在庫B','0');
INSERT INTO `ZAIKO_INFO` (`ZAIKO_ID`, `ITEM_NAME`, `SAKJYO_FLG` ) VALUES ('Z003','在庫C','0');
INSERT INTO `ZAIKO_INFO` (`ZAIKO_ID`, `ITEM_NAME`, `SAKJYO_FLG` ) VALUES ('Z004','在庫D','0');
INSERT INTO `ZAIKO_INFO` (`ZAIKO_ID`, `ITEM_NAME`, `SAKJYO_FLG` ) VALUES ('Z005','在庫E','0');
INSERT INTO `ZAIKO_INFO` (`ZAIKO_ID`, `ITEM_NAME`, `SAKJYO_FLG` ) VALUES ('Z006','在庫F','0');

INSERT INTO `ITEM_INFO_GROUP` (`ITEM_ID_GROUP`, `ITEM_ID`, `SAKJYO_FLG` ) VALUES ('IIG001','I001','0');
INSERT INTO `ITEM_INFO_GROUP` (`ITEM_ID_GROUP`, `ITEM_ID`, `SAKJYO_FLG` ) VALUES ('IIG001','I002','0');
INSERT INTO `ITEM_INFO_GROUP` (`ITEM_ID_GROUP`, `ITEM_ID`, `SAKJYO_FLG` ) VALUES ('IIG001','I003','0');
INSERT INTO `ITEM_INFO_GROUP` (`ITEM_ID_GROUP`, `ITEM_ID`, `SAKJYO_FLG` ) VALUES ('IIG002','I004','0');
INSERT INTO `ITEM_INFO_GROUP` (`ITEM_ID_GROUP`, `ITEM_ID`, `SAKJYO_FLG` ) VALUES ('IIG002','I009','0');
INSERT INTO `ITEM_INFO_GROUP` (`ITEM_ID_GROUP`, `ITEM_ID`, `SAKJYO_FLG` ) VALUES ('IIG002','I010','0');
INSERT INTO `ITEM_INFO_GROUP` (`ITEM_ID_GROUP`, `ITEM_ID`, `SAKJYO_FLG` ) VALUES ('IIG003','I005','0');
INSERT INTO `ITEM_INFO_GROUP` (`ITEM_ID_GROUP`, `ITEM_ID`, `SAKJYO_FLG` ) VALUES ('IIG003','I006','0');

INSERT INTO `ZAIKO_INFO_GROUP` (`ZAIKO_ID_GROUP`, `ZAIKO_ID`, `SAKJYO_FLG` ) VALUES ('ZIG001','Z001','0');
INSERT INTO `ZAIKO_INFO_GROUP` (`ZAIKO_ID_GROUP`, `ZAIKO_ID`, `SAKJYO_FLG` ) VALUES ('ZIG001','Z002','0');
INSERT INTO `ZAIKO_INFO_GROUP` (`ZAIKO_ID_GROUP`, `ZAIKO_ID`, `SAKJYO_FLG` ) VALUES ('ZIG001','Z003','0');
INSERT INTO `ZAIKO_INFO_GROUP` (`ZAIKO_ID_GROUP`, `ZAIKO_ID`, `SAKJYO_FLG` ) VALUES ('ZIG002','Z004','0');
INSERT INTO `ZAIKO_INFO_GROUP` (`ZAIKO_ID_GROUP`, `ZAIKO_ID`, `SAKJYO_FLG` ) VALUES ('ZIG002','Z009','0');
INSERT INTO `ZAIKO_INFO_GROUP` (`ZAIKO_ID_GROUP`, `ZAIKO_ID`, `SAKJYO_FLG` ) VALUES ('ZIG002','Z010','0');
INSERT INTO `ZAIKO_INFO_GROUP` (`ZAIKO_ID_GROUP`, `ZAIKO_ID`, `SAKJYO_FLG` ) VALUES ('ZIG003','Z007','0');
INSERT INTO `ZAIKO_INFO_GROUP` (`ZAIKO_ID_GROUP`, `ZAIKO_ID`, `SAKJYO_FLG` ) VALUES ('ZIG003','Z002','0');

INSERT INTO `ZAIKO_CHECK` (`ZAIKO_ID`, `SAKJYO_FLG` ) VALUES ('Z001','0');
INSERT INTO `ZAIKO_CHECK` (`ZAIKO_ID`, `SAKJYO_FLG` ) VALUES ('Z002','0');
INSERT INTO `ZAIKO_CHECK` (`ZAIKO_ID`, `SAKJYO_FLG` ) VALUES ('Z003','0');
INSERT INTO `ZAIKO_CHECK` (`ZAIKO_ID`, `SAKJYO_FLG` ) VALUES ('Z004','0');
INSERT INTO `ZAIKO_CHECK` (`ZAIKO_ID`, `SAKJYO_FLG` ) VALUES ('Z005','0');
INSERT INTO `ZAIKO_CHECK` (`ZAIKO_ID`, `SAKJYO_FLG` ) VALUES ('Z006','0');

補足情報（FW/ツールのバージョンなど）

MySqlのバージョンは6.3になります。

期待する結果は以下です

ZAIKO_ID	ITEM_ID
Z001	I006
Z002	I001,I002,I006
Z003	I006

※ZAIKO_IDに対して紐付いているITEM_IDをすべてカンマ区切りで出力
※ZAIKO_IDが存在しないものは空

yambejp

2021/03/18 10:57

> MySqlのバージョンは6.3になります何をみてそう判断しましたか？

退会済みユーザー

2021/03/18 10:59

MySQL Workbench→上部のメニューバーのHelp→About Workbenchで確認しました。

行動規範の内容に同意します

回答1件

ZAIKO_CHECKというテーブルが用意されていません
またcreate table 内でカンマがなくてエラーになっている箇所があります

投稿2021/03/18 11:02

yambejp

総合スコア116661

退会済みユーザー

2021/03/18 11:10

大変失礼いたしました。追記・修正しましたのでご確認いただけますでしょうか？

yambejp

2021/03/18 11:36

結果としてどういった出力を想定していますか？

退会済みユーザー

2021/03/18 11:53

説明が足らずすみません、質問内容に期待する結果を追記させていただきました。

yambejp

2021/03/18 12:14

Z001 I009,I010 Z002 I009,I010,I015 Z003 の根拠はどういうロジックなのでしょうか？

退会済みユーザー

2021/03/18 12:36

記載したSQLの結果のZAIKO_IDが例えば「Z001」のみだった場合、 ITEM_INFOのZAIKO_IDが「Z001」のITEM_ID、 ITEM_INFOのITEM_ID_GROUPの中のITEM_IDに紐付くZAIKO_IDが「Z001」のITEM_ID、 ITEM_INFOのZAIKO_ID_GROUPの中のZAIKO_IDが「Z001」のITEM_ID、を、すべてカンマ区切りで出力させたいです。

yambejp

2021/03/18 12:44

item_infoのzaiko_idはz002ですよね？データと想定結果がリンクしてないのでは？

退会済みユーザー

2021/03/18 13:10

すみません、INSERT文と想定結果内容を修正させていただきました！

yambejp

2021/03/18 13:17

ITEM_INFO_GROUPのinsertがprimaryの競合で失敗します

退会済みユーザー

2021/03/18 13:50 編集

ITEM_INFO_GROUPテーブルは ITEM_ID_GROUPとITEM_IDがPKとなっていますが、 INSERT文で記載した通り、同じITEM_ID_GROUP（IIG001等）で複数のレコードを持たせることで ITEM_IDをグループ化しています…