MySQLにmembers,subjects,colorsの3つのテーブルがあり、memberには名前データ、subjectsには好きな教科データ、colorsには好きな色のデータが入っています。membersのidとsubjectsのmember_id、membersのidとcolorsのmember_idが結合キーになっています。
3つのテーブルを連結させてAND検索、OR検索を行いたいと思います。
2つのテーブルのAND検索OR検索については以前ここで質問して回答を頂きました。(https://oshiete.goo.ne.jp/qa/9128029.html)
これを応用して3つのテーブルで検索を行おうとしているのですがうまくいかないので、やり方を教えていただければと思います。
「求める結果」
以下の4パターンのSQL文を作成したいです。
1.指定する教科と色のどれか1つにでも該当
例)国語 英語 青 緑 のどれか1つに該当する名前を検索
2.教科はAND 色はOR
例)国語と英語の両方に該当し、かつ、青か緑のどれか1つにでも該当する名前を検索
3.教科はOR 色はAND
例)国語と英語のどれか1つにでも該当し、かつ、青と緑の両方に該当する名前を検索
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ベストアンサー
- 回答日時:
//元データ
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.指定する教科と色のどれか1つにでも該当
>例)国語 英語 青 緑 のどれか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
>例)国語と英語の両方に該当し、かつ、青か緑のどれか1つにでも該当する名前を検索
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
>例)国語と英語のどれか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('国語','英語')
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さま
ご回答ありがとうございます。
4つのパターンを試し、求める結果を得ることができました。
3つのテーブルの結合とデータの抽出方法が理解できました。何度かSQL文を書いてみて、自分でも一から書けるようにしたいと思います。
いつも助けていただき本当にありがとうございます。
今後ともよろしくお願い致します。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- MySQL 書籍の内容はまともでしょうか? 1 2023/01/22 03:07
- Access(アクセス) アクセス where句を使用して複数条件抽出をするには 2 2022/08/29 13:24
- Access(アクセス) アクセス 削除するレコードを含んだテーブルを指定してくださいのエラー対処方法 1 2022/11/24 15:01
- Access(アクセス) アクセス 有効なフィールド名、または式として認識できませんのエラー 3 2022/08/19 11:53
- MySQL PHPとMySQLを使った掲示板の作り方 1 2022/06/02 13:00
- Oracle SQL update方法 2 2022/06/22 14:07
- PostgreSQL 画像とカテゴリーを出力したいのですが、取得の条件を付ける方法がわかりません。 2 2022/05/01 18:03
- SQL Server [SQLServer] テーブル名からカラム名を取得する 1 2022/08/23 21:20
- C言語・C++・C# C言語 プログラミング 4 2022/05/22 11:53
- Oracle sqlで質問です。 aテーブルとbテーブルがあり、下記のsqlで取得したidとnameに一致しないレ 1 2022/04/20 20:34
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
[MySQL] 3つのテーブルの結合で...
-
エクセルの関数について教えて...
-
sqlで、600行あるテーブルを100...
-
SQLサーバから、項目の属性(型...
-
上位3位を求めるSQL文は?
-
副問合せの書き方について
-
SQL Left Join で重複を排除す...
-
SQLにて特定の文字を除いた検索...
-
[MySQL] UNIQUE制約の値を更新...
-
Access パラメータクエリをcsv...
-
selectした大量データをinsert...
-
親と子供が複数のSQL取得方法
-
テーブルが5つの時の結合の仕...
-
書籍の内容はまともでしょうか?
-
クエリ表示と、ADOで抽出したレ...
-
【SQL文】Insert into文で文法...
-
VIEWの元のテーブルのindexって...
-
inner joinをすると数がおかし...
-
Mysqlでunionを使った検索速度...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
SQL Left Join で重複を排除す...
-
SQLサーバから、項目の属性(型...
-
副問合せの書き方について
-
VIEWの元のテーブルのindexって...
-
エクセルの関数について教えて...
-
SQLにて特定の文字を除いた検索...
-
マイクラPC版のコマンドで効率...
-
select文のwhere句に配列を入れ...
-
sqlで、600行あるテーブルを100...
-
ある条件の最大値+1を初番する...
-
inner joinをすると数がおかし...
-
Unionした最後にGROUP BYを追加...
-
期間の重複を調べるSQL文につい...
-
クエリ表示と、ADOで抽出したレ...
-
Access パラメータクエリをcsv...
-
PL/SQLの変数について
-
MySQLのDATE型カラム値がNULLの...
-
php+mysqlで複数選択削除について
-
上位3位を求めるSQL文は?
おすすめ情報