php select SQL文

質問

出席者、欠席者、未登録者の一覧を出したいのですが、SQL文に困り投稿しました。

スケジュールと出欠登録とユーザーの各テーブルがDB内に入っています。

スケジュールのテーブルの中には
schedule
|id|date|naiyou|
|1|2012-07-10|A|
|2|2012-08-10|B|

出欠登録のテーブルの中には
entry
|id|schedule_id|member_id|entry|
|1|1|1|1|
|2|1|2|2|
|3|2|3|1|
|4|2|2|1|

メンバーのテーブルの中には
member
|id|name|registdate|
|1|AAA|2012-06-10|
|2|BBB|2012-07-01|
|3|CCC|2012-07-30|

とあった場合
出席者、欠席者の一覧は簡単に出せるのですが、未登録者の一覧が出せず困っています。
また、出席者や欠席者、未登録者の一覧はそのスケジュールIDごとに分かれ一覧をだしています。
例：
スケジュールid1の場合、
出席者　AAA
欠席者　BBB

未登録者はそのスケジュールにまだ出欠登録をされていなければ一覧表示し、
出欠登録をされたら未登録者から消え、出席者か欠席者に表示される。
また、スケジュールの日程(schedule.date)より前にメンバーの登録(member.registdate)をした人のみ一覧表示する。

スケジュールid1の場合、
出席者　AAA
欠席者　BBB

↑CCCは未登録者だが、schedule.dateより後にメンバー登録をしているため表示しない

スケジュールid2の場合、
出席者　CCC、BBB
未登録者　AAA

以上の内容を踏まえて下記のように書いているのですが、わからなくなってしまいました。
どなたかおわかりの方がいらっしゃいましたらご教示お願いします。

select *
from schedule
left join entry on schedule.id = entry.schedule_id
right join member on entry.member_id = member.id
where entry.`schedule_id` = 2 AND schedule.date >= member.registdate

上記の書き方では shedule.id 2に出欠登録した人がでてきて、未登録者がでてこないです。
未登録者の一覧を出す方法をお願いします。

回答No.2

yambejp · ベストアンサー

ひさしぶりです
お盆休みのバタバタで回答できず、すみません。

＞できればスケジュール日程より前にメンバー登録した人を表示させたいです

整理しましょう。
出席・欠席を表明している人はメンバー登録しているということで前述の
SQLで必要十分であるということでよろしいですね？
そうなると残るは未登録者についてですが、ちょっと工夫すればよさそうです

わかりやすくするためにまず、memberにDDD、EEEを追加して試してみます
insert into member values(1,'AAA','2012-06-10'),(2,'BBB','2012-07-01'),(3,'CCC','2012-07-30'),(4,'DDD','2012-01-01'),(5,'EEE','2012-12-31');

以下、もうすこし効率的な書き方がありそうなきがしますが
とりあえずこんな感じでいけるはずです。

//スケジュール1の未登録者
select name from member inner join (select 1 as sid) as sub on 1
left join entry on member_id=mid and schedule_id=sid
inner join schedule on schedule.sid=sub.sid and d>=registdate
where schedule_id is null;

→DDDが表示されます

//スケジュール2の未登録者
select name from member inner join (select 2 as sid) as sub on 1
left join entry on member_id=mid and schedule_id=sid
inner join schedule on schedule.sid=sub.sid and d>=registdate
where schedule_id is null;

→AAAとDDDが表示されます

※注：(select 2 as sid)の数字にsidにあたる数字を入れてください

お礼

お返事が遅くなり申し訳ありません。
知らないSQL文がでてきているので調べながら確認しておりました。

上記の内容でできることが確認できました。

本当に有難うございました。

回答No.1

yambejp

ちょっとわかりづらいのでカラム名を調整してあります

create table schedule(sid int,d date,naiyou varchar(10));
insert into schedule values(1,'2012-07-10','A'),(2,'2012-08-10','B');
create table entry(eid int,schedule_id int,member_id int,entry int);
insert into entry values(1,1,1,1),(2,1,2,2),(3,2,3,1),(4,2,2,1);
create table member(mid int,name varchar(10),registdate date);
insert into member values(1,'AAA','2012-06-10'),(2,'BBB','2012-07-01'),(3,'CCC','2012-07-30');

//スケジュール1
select name from member inner join entry on member_id=mid and schedule_id=1 and entry=1;
select name from member inner join entry on member_id=mid and schedule_id=1 and entry=2;
select name from member left join entry on member_id=mid and schedule_id=1 where schedule_id is null;

//スケジュール2
select name from member inner join entry on member_id=mid and schedule_id=2 and entry=1;
select name from member inner join entry on member_id=mid and schedule_id=2 and entry=2;
select name from member left join entry on member_id=mid and schedule_id=2 where schedule_id is null;

お礼

ご回答有難う御座います。
解りやすいselect文有難う御座います。

ただ1点なのですが、未登録者のselect文ですが、このままですと
スケジュールの日程より後にメンバー登録した人まで表示されてしまいます。

上記の内容は普通では特に気にしないとこなのですが、メンバーに過去のスケジュールの出席者、欠席者、未登録者を見せるため必要になってしまいます。

できればスケジュール日程より前にメンバー登録した人を表示させたいです(一番目的としている)。
よろしければお願いします。