mysqlで3つのテーブルを使った（INNER JOIN)データの抽出方法

MySQLにmembers,subjects,colorsの３つのテーブルがあり、memberには名前データ、subjectsには好きな教科データ、colorsには好きな色のデータが入っています。membersのidとsubjectsのmember_id、membersのidとcolorsのmember_idが結合キーになっています。
３つのテーブルを連結させてAND検索、OR検索を行いたいと思います。

２つのテーブルのAND検索OR検索については以前ここで質問して回答を頂きました。（https://oshiete.goo.ne.jp/qa/9128029.html）
これを応用して３つのテーブルで検索を行おうとしているのですがうまくいかないので、やり方を教えていただければと思います。

「求める結果」
以下の４パターンのSQL文を作成したいです。
1.指定する教科と色のどれか１つにでも該当
例）国語 英語 青 緑 のどれか１つに該当する名前を検索
2.教科はAND 色はOR
例）国語と英語の両方に該当し、かつ、青か緑のどれか１つにでも該当する名前を検索
3.教科はOR 色はAND
例）国語と英語のどれか１つにでも該当し、かつ、青と緑の両方に該当する名前を検索
4.指定する教科と色のすべてを満たす
例）国語 英語 青 緑 のすべてを満たす名前を検索

「作成したSQL」
（科目のAND)
SELECT m.id,m.name FROM members as m INNER JOIN subjects as s on m.id = s.member_id AND s.subject in('国語','英語') Group By m.id Having count(distinct s.subject)=2

（科目のOR)
SELECT m.id,m.name FROM members as m INNER JOIN subjects as s on m.id = s.member_id AND s.subject in('国語','英語') Group By m.id

（色のAND)
SELECT m.id,m.name FROM members as m INNER JOIN colors as c on m.id = c.member_id AND c.color in('青','緑') Group By m.id Having count(distinct c.color)=2

（色のOR）
SELECT m.id,m.name FROM members as m INNER JOIN colors as c on m.id = c.member_id AND c.color in('青','緑') Group By m.id
これらを組み合わせてSQLを作れば良いと思うのですが、うまくいきません。


「テーブル構成」
[membersテーブル]（名前）
id (int) auto_increment
name (varchar)


[subjectsテーブル]（科目）
id (int) auto_increment
member_id(int)
subject (varchar)


[colorsテーブル]（色）
id (int) auto_increment
member_id
color (varchar)

「入力されているデータ例」
[membersテーブル]
id|name
1[山田
2|鈴木
3|田中

[subjectsテーブル]
id|member_id|subject
1 |1| 国語
2 |1| 数学
3 |1| 英語
4 |2| 国語
5 |2| 英語
6 |3| 数学

[colorsテーブル]
id|member_id|color
1 |1| 赤
2 |1| 青
3 |2| 赤
4 |2| 緑
5 |3| 赤

No.1 ベストアンサー 回答者： yambejp 回答日時： 2016/02/05 17:27

//元データ

create table members(id int not null unique,name varchar(30),index id_name(id,name));
insert into members values(1,'山田'),(2,'鈴木'),(3,'田中');

create table subjects(id int not null primary key,member_id int,subject varchar(30),index member_subject(member_id,subject));
insert into subjects values(1,1,'国語'),(2,1,'数学'),(3,1,'英語'),(4,2,'国語'),(5,2,'英語'),(6,3,'数学');

create table colors(id int not null primary key,member_id int, color varchar(30),index member_color(member_id,color));
insert into colors values(1,1,'赤'),(2,1,'青'),(3,2,'赤'),(4,2,'緑'),(5,3,'赤');

＞1.指定する教科と色のどれか１つにでも該当
＞例）国語 英語 青 緑 のどれか１つに該当する名前を検索

SELECT DISTINCT m.id,m.name FROM members as m
LEFT JOIN subjects as s on m.id = s.member_id
AND s.subject in('国語','英語')
LEFT JOIN colors as c on m.id = c.member_id
AND c.color in('青','緑')
WHERE not(s.member_id is null and c.member_id is null)

＞2.教科はAND 色はOR
＞例）国語と英語の両方に該当し、かつ、青か緑のどれか１つにでも該当する名前を検索

SELECT DISTINCT m.id,m.name FROM members as m
LEFT JOIN colors as c on m.id = c.member_id
AND c.color in('青','緑')
WHERE 1
and not( c.member_id is null)
and m.id in(
SELECT member_id FROM subjects
WHERE subject in('国語','英語')
GROUP BY member_id
HAVING COUNT(*)=2
)

＞3.教科はOR 色はAND
＞例）国語と英語のどれか１つにでも該当し、かつ、青と緑の両方に該当する名前を検索

SELECT DISTINCT m.id,m.name FROM members as m
LEFT JOIN subjects as s on m.id = s.member_id
AND s.subject in('国語','英語')
WHERE 1
and not( s.member_id is null)
and m.id in(
SELECT member_id FROM colors
WHERE color in('青','緑')
GROUP BY member_id
HAVING COUNT(*)=2
)

＞4.指定する教科と色のすべてを満たす
＞例）国語 英語 青 緑 のすべてを満たす名前を検索

SELECT DISTINCT m.id,m.name FROM members as m
WHERE 1
and m.id in(
SELECT member_id FROM subjects
WHERE subject in('国語','英語')
GROUP BY member_id
HAVING COUNT(*)=2
)
and m.id in(
SELECT member_id FROM colors
WHERE color in('青','緑')
GROUP BY member_id
HAVING COUNT(*)=2
)

この回答へのお礼

yambejpさま

ご回答ありがとうございます。
４つのパターンを試し、求める結果を得ることができました。
３つのテーブルの結合とデータの抽出方法が理解できました。何度かSQL文を書いてみて、自分でも一から書けるようにしたいと思います。
いつも助けていただき本当にありがとうございます。
今後ともよろしくお願い致します。

お礼日時：2016/02/06 00:14