複数のテーブルを連結する複雑なSQLについてお力お貸しください

Question

SQL（MySQL）を書いておりますが、複雑になり煮詰まっております。
お力お貸しください。

仕様：
・親イベントに複数の子イベント（数は決まっていない）がある
・子イベントごとに出欠や入金の状態を管理する
・イベント参加状況管理テーブルは出欠登録処理をするとレコードが作成される

出力する表について：
・全ユーザーの状態を出力する
・出欠登録をしていないユーザーの出欠・入金状況は（レコードが無いので）NULLで取得する
・？または―の表示はDBからの取得時には特に考えない

---

ER図

出力したい表

Comments

[takotakot]

イベントの数は3つ固定でしょうか。複数のものを横に並べる(イベントが3つ並んでいます)のは、横持ちで、よくないパターンとされていますし、SQL で横の列数を可変にするのは困難です。

[yuki84web]

ありがとうございます。可変となっていますが、設計自体を見直した方がよさそうですね・・

[takotakot]

DB 設計は間違っていないと思いますので、取得するアプリ側で、イベント数分ループをまわす感じでしょうか。

Answer 1

これって親イベントはホントにいるのでしょうか？
子イベントとユーザーテーブルをリレーションする中間テーブルが
あればよいような気がします。

create tableとinsert into形式で例示いただければ
SQL文は知恵を絞れると思います。

sample

• 元データ

create table event (eid int unique,ename varchar(30));
insert into event values(1,'child event 1'),(2,'child event 2'),(3,'child event 3');
create table user (uid int unique,uname varchar(30));
insert into user values(1,'tanaka'),(2,'yoshida'),(3,'suzuki'),(4,'sato'),(5,'honda');
create table event_user(eid int,uid int ,shukketu tinyint not null,nyukin tinyint not null,unique(eid,uid));
insert into event_user values(1,1,1,1),(1,2,0,0),(1,3,1,0),(2,1,0,1),(3,4,1,0);

• 検索

select t1.uid,t1.uname
,sum(t2.eid=1 * t2.shukketu=1) as e1s
,sum(t2.eid=1 * t2.nyukin=1) as e1n
,sum(t2.eid=2 * t2.shukketu=1) as e2s
,sum(t2.eid=2 * t2.nyukin=1) as e2n
,sum(t2.eid=3 * t2.shukketu=1) as e3s
,sum(t2.eid=3 * t2.nyukin=1) as e3n
 from user as t1
left join event_user as t2 on t1.uid=t2.uid
group by t1.uid

プロシージャとピボットとか使うともうちょい自動化してそれっぽく見えるかも

procudure

• 以下、実行しプロシージャをつくっておきます。

drop procedure if exists my_proc;
delimiter //
create procedure my_proc()
begin
declare a int;
declare b varchar(100);
declare done int default 0;
declare cur cursor for
select eid,ename from event order by eid;
declare continue handler for sqlstate '02000' set done=1;

set @sql='select t1.uid,t1.uname\n';
open cur;
repeat
fetch cur into a,b;
if not done then
set @sql=concat (@sql,',sum(t2.eid=',a,' * t2.shukketu=1) as `',b,' shukketu`\n');
set @sql=concat (@sql,',sum(t2.eid=',a,' * t2.nyukin=1) as `',b,' nyukin`\n');
end if;
until done end repeat;
close cur;
set @sql=concat (@sql,' from user as t1\n');
set @sql=concat (@sql,' left join event_user as t2 on t1.uid=t2.uid\n');
set @sql=concat (@sql,' group by t1.uid\n');
prepare stmt from @sql;
execute stmt;
end
//
delimiter ;

• 実行

call my_proc;

Comments

[yuki84web]

ありがとうございます。親イベントの情報も画面に出力するので連結しています。子イベントとユーザーテーブルをリレーションする中間テーブルというのは図で示している参加状況管理テーブルとは違うものでしょうか。無理に一気に取ってこようとせず個別に配列で取ってきて、アプリ側で連結した方がスマートなのかと考えています。

[yuki84web]

SQLの提示ありがとうございます。
サンプルありがとうございます。頂いたものですと子イベント3つまでの表示になりますので、この部分は子イベントの数だけループ処理で文を動的に生成することにします。
ただ、表を出力するページは特定の親イベントの詳細ページになるので、子イベントIDの対象を絞る必要があります。（例えば親イベントID=1に子イベントID=1,2,3、親イベントID=2に子イベントID=4,5,6がある場合、親イベントID=2の子イベントの情報が欲しいときなど）この場合はさらにテーブルを連結するのでしょうか？

[yambejp]

最初にも一部書きましたが仕様がきちんと書かれていないのでなんとも回答しようがありません。
とにかく具体的なテーブルのサンプルを提示してください
プロシージャのサンプルはあげておきました

[yuki84web]

提示している図などは実際開発しているものを簡略化して作成したもので、サンプルを用意するのが時間が掛かりそうなため、一旦クローズとさせていただきます。回答頂きましてありがとうございました。