マスタに存在する全IDの月別集計表を出力したい

Question

###質問内容
ユーザーマスタTABLE2 に存在する全員の集計表(下記のRESULT_TABLE)を出力したいです。

実績データテーブルTABLE1 には、ユーザーマスタ全員のデータが毎回登録されるわけではありません。
(本質問にて提示のデータですと、ユーザーマスタには ID=C がありますが、実績データテーブルには登録がありません。そのような実績のない ID は O と出力したいのです）

どのように書けばよいかわかりませんので
ご教示お願いいたします。

###期待する出力結果
(RESULT_TABLE):出力したいテーブル

MONTH	ID	DATA
1	A	202
1	B	204
1	C	0
2	A	210
2	B	212
2	C	0

###テーブル
(TABLE1):実績データが登録される。日を経るごとにデータは追加される。

MONTH	DAY	ID	DATA
1	1	A	100
1	1	B	101
1	2	A	102
1	2	B	103
2	1	A	104
2	1	B	105
2	2	A	106
2	2	B	107

(TABLE2):マスタ

ID	NAME
A	001
B	002
C	003

###前提条件
MySQLを用いています。
TABLE1, TABLE2 は下記でCREATEしています。

CREATE TABLE `TABLE1` (
  `MONTH` int(2) NOT NULL,
  `DAY` int(2) NOT NULL,
  `ID` char(4) NOT NULL,
  `DATA` int(4) DEFAULT NULL,
  PRIMARY KEY (`MONTH`,`DAY`,`ID`)
) ;
INSERT INTO `TABLE1` VALUES
(1,1,'A',100),
(1,1,'B',101),
(1,2,'A',102),
(1,2,'B',103),
(2,1,'A',104),
(2,1,'B',105),
(2,2,'A',106),
(2,2,'B',107);

CREATE TABLE `TABLE2` (
  `ID` char(4) NOT NULL,
  `NAME` char(4) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ;
INSERT INTO `TABLE2` VALUES
('A','001'),
('B','002'),
('C','003');

###試したこと
TABLE2と、TABLE1を月別集計したものをLEFT OUTER JOINしますが、MONTH列があるため、下記のようにID=Cは1レコードのみとなってしまいます。

MONTH	ID	DATA
NULL	C	NULL
...	...	...

Comments

[Orlofsky]

テーブル定義とデータは CREATE TABLE文とINSERT文に修正できると動作確認し易いので、適切なコメントが付き易いです。

[Orlofsky]

>TABLE2と、TABLE1を月別集計したものをLEFT OUTER JOINしますが、MONTH列があるため、下記のようにID=Cは1レコードのみとなってしまいます。

その時のSELECT文も追記しては？

Answer 1

在る月でテーブル増やして

select MONTH, ID, coalesce(DATA,0) DATA from
  (select MONTH, ID from TABLE2,
    (select distinct MONTH from TABLE1) TBL3
   ) TBL4
left join
  (select MONTH, ID, sum(DATA) AS DATA from TABLE1 group by MONTH,ID) TBL5
using(MONTH,ID) order by MONTH,ID;

Comments

[nickow]

回答内のSQL文で期待する出力結果を得ることができました。
回答ありがとうございました。
こちらの内容でも同じ結果を得ることができましたが、ベストアンサーは回答の早かった方を選択させていただきました。

Answer 2

1月,2月のCのデータが無く月を判断することができないのでそのままだと無理です

Comments

[yambejp]

無理やりやるならこんな感じ
create table datas(month int,day int,id varchar(10),data int);
insert into datas values
(1,1,'A',100),
(1,1,'B',101),
(1,2,'A',102),
(1,2,'B',103),
(2,1,'A',104),
(2,1,'B',105),
(2,2,'A',106),
(2,2,'B',107);

create table master(id varchar(10),name varchar(10));
insert into master values
('A','001'),
('B','002'),
('C','003');

[yambejp]

select t2.month,t1.id,t1.name,coalesce(t3.data,0) as data
from master as t1
inner join (select distinct month from datas) as t2 on 1
left join (select month,id,sum(data) as data from datas group by month,id) as t3
on t1.id=t3.id and t2.month=t3.month
order by month,id

[nickow]

回答内のSQL文で期待する出力結果を得ることができました。
回答ありがとうございました。