mysql 多対多リレーション時のデータ呼び出しについて

Question

mysql 多対多リレーション時のデータ呼び出しについて質問いたします。

以下のような構造になっていたとします。

###table1(料理)

--------------------------------
| table1_id | table1_name |
--------------------------------
| 1 | オムレツ |
--------------------------------
| 2 | オムライス |
--------------------------------
| 3 | チキンライス |
--------------------------------

###table12(中間テーブル)

--------------------------------
| table1_id | table2_id |
--------------------------------
| 1 | 1|
--------------------------------
| 1 | 2 |
--------------------------------
| 2 | 1 |
--------------------------------
| 2 | 2 |
--------------------------------
| 2 | 3 |
--------------------------------
| 3 | 2 |
--------------------------------
| 3 | 3 |
--------------------------------

###table2(食材)

--------------------------------
| table2_id | table2_name |
--------------------------------
| 1 | タマゴ |
--------------------------------
| 2 | ケチャップ |
--------------------------------
| 3 | ご飯 |
--------------------------------

###以下のようにJOINしています。

SELECT * FROM `table1` AS `t1`

LEFT JOIN `table12` AS `t12`
	ON `t12`.`table1_id` = `t1`.`table1_id`
LEFT JOIN `table2` AS `t2`
	ON `t2`.`table2_id` = `t12`.`table2_id`

	GROUP BY `t1`.`table1_id`

以下を条件のセレクト文はどのように記述すればよいのでしょうか？

質問１.タマゴ OR ケチャップ を使用している料理

**質問２.タマゴ AND ケチャップ **を使用している料理

よろしくお願いいたします。

Comments

[yambejp]

「タマゴ」や「ケチャップ 」で検索するのは名称を利用するのでしょうか？それともidを指定していいのでしょうか？

[web_yasu]

idを指定大丈夫です。

Answer 1

以下例示します
元データ

create table ryouri(rid int not null primary key,name varchar(20));
insert into ryouri values(1,'オムレツ'),(2,'オムライス'),(3,'チキンライス'),(4,'お茶漬け');

create table shokuzai(sid int not null primary key,name varchar(20));
insert into shokuzai values(1,'タマゴ'),(2,'ケチャップ'),(3,'ご飯');

create table relation_r_s(rid int,sid int,unique(rid,sid));
insert into relation_r_s values(1,1),(1,2),(2,1),(2,2),(2,3),(3,2),(3,3),(4,3);

考え方

• リレーションテーブルにおいてridに食材が何種類使われているか検証

select rid,count(*)  from relation_r_s
group by rid

• タマゴとケチャップで絞り込む

select rid,count(*)  from relation_r_s
where sid in (1,2)
group by rid

条件：

• タマゴorケチャップはcount(*)>0
• タマゴandケチャップはcount(*)=2
• タマゴ、ケチャップどちらか一つならcount(*)=1

したがってhavingを使って

/*タマゴorケチャップ*/
select * from ryouri
where rid in(
select rid  from relation_r_s
where sid in (1,2)
group by rid
having count(*)>0
);

/* タマゴandケチャップ */
select * from ryouri
where rid in(
select rid  from relation_r_s
where sid in (1,2)
group by rid
having count(*)=2
);

Comments

[web_yasu]

クリエイト文まで作成いただいており、テストデータのお茶漬けまで。
考え方が箇条書きにされていて分かりやすかったです。
回答ありがとうございました。ベストアンサーにさせていただきます。

Answer 2

質問１.タマゴ OR ケチャップ を使用している料理

select distinct table1_name from table1 join table12 using(table1_id)
 join table2 using(table2_id) where table2_name in ('タマゴ','ケチャップ');

質問２.タマゴ AND ケチャップ を使用している料理

select distinct table1_name from (select table1_id, table1_name from table1
  join table12 t12 using(table1_id) join table2 t2 on t12.table2_id=t2.table2_id
       and t2.table2_name='ケチャップ') t10 
join (select table1_id from table1 join table12 t12 using(table1_id)
  join table2 t2 on t12.table2_id=t2.table2_id 
       and t2.table2_name='タマゴ') t11 using(table1_id);

Comments

[web_yasu]

「質問２.タマゴ AND ケチャップ」のJOINが増えていく考え方も参考になりました。データが多くなった状況で他の方法と比べてみたいと思いました。
回答ありがとうございました。

Answer 3

料理がとれればいいということで、とりあえず最後の SELECT 文は無視しておきます。

質問1 は

SELECT DISTINCT table1.table1_id, table1.table1_name
FROM table1 INNER JOIN table12 ON (table12.table1_id = table1.id)
INNER JOIN table2 ON (table2.id = table12.table2_id)
WHERE table2.table2_id in (1, 2);

でいいですね。

質問2 はちょっと面倒ですが、「table12で、条件に合う食材の件数をID毎にカウントして、そのカウント数が食材の総数と一致するtable1_id を抽出」できればいいはずです。

SELECT table1.table1_id, table1.table1_name
FROM table1 INNER JOIN (
  SELECT table1_id FROM table12 WHERE table2_id IN (1,2)
  GROUP BY table1_id
  HAVING count(*)=2
) vt1 ON (table1.table1_id = vt1.table1_id)

Comments

[web_yasu]

「table12で、条件に合う食材の件数をID毎にカウントして、そのカウント数が食材の総数と一致するtable1_id を抽出」
という考え方で応用できそうです。
回答ありがとうございました。

Answer 4

idでいいなら

select distinct table1_id from table12 where table2_id=1 and(or) table2_id=2

Comments

[web_yasu]

回答ありがとうございました。