mysplで取得したいデータの取り方がわかりません

Question

mysplで取得したいデータの取り方がわかりません

CREATE TABLE `db_name`.`monsters` 
(
 `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'モンスターid' ,
 `monster_name` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'モンスター名' ,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

CREATE TABLE `db_name`.`wazas` 
(
 `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '技id' ,
 `waza_name` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '技名' ,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

CREATE TABLE `db_name`.`learn`
(
 `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '覚える技id' ,
 `monster_id` INT(11) NOT NULL DEFAULT '0' COMMENT 'モンスターid' ,
 `waza_id` INT(11) NOT NULL COMMENT '技id' ,
 PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

INSERT INTO
.monsters
 (monster_name)
VALUES
 ("スライム"),
 ("ゴブリン"),
 ("ネクロマンサー"),
 ("オーク"),
 ("キングスライム");

INSERT INTO
.wazas
 (waza_name)
VALUES
 ("たいあたり"),
 ("きりさく"),
 ("ファイアー"),
 ("サンダー"),
 ("自己再生");

INSERT INTO
.learn
 (monster_id,waza_id)
VALUES
 ("1","1"),
 ("1","4"),
 ("2","1"),
 ("2","2"),
 ("3","1"),
 ("3","3"),
 ("3","4"),
 ("3","5"),
 ("4","3"),
 ("4","5");

簡略版ですが、上記の様なdbがあります
検索画面を作りたくて複数の指定した技を覚えるモンスターを取得したいのですが、うまくいきません
理想では、
検索画面でファイアーとサンダーを覚えるモンスターで検索したら、ファイアーとサンダー両方覚えることのできる、ネクロマンサーだけが検索に出てきて欲しいです
monstersとlearnをjoinしたらいけそうだと思ったのですが、技を複数指定する方法が分かりませんでした
実際のデータはモンスターと技は数百、覚える技のデータは数万ほどになります
できる限り、一発のsqlで素早く取得したいです
dbの設計をこうした方がいいなどのアドバイスなどもあればお願いします
どうかご教授よろしくお願いします

Comments

[退会済みユーザー]

DBの定義はCREATE文で提示してください。

[gottoteru]

ご指摘ありがとうございます、申し訳ございません、CREATE 文の追記いたしました

[退会済みユーザー]

データを作成するINSERTもよろしく

[gottoteru]

INSERT文追記しました

[Orlofsky]

myspl → MySQL

Answer 1

SELECT M.* FROM monsters M
INNER JOIN learn L ON M.`id` = L.`monster_id`
WHERE L.`waza_id` IN (3, 4)
GROUP BY M.id
HAVING count(L.`waza_id`) = 2;

前提条件が変わったので

SELECT M.* FROM monsters M
INNER JOIN learn L ON M.`id` = L.`monster_id`
WHERE L.`waza_id` IN (3, 4)
GROUP BY M.id
HAVING count(distinct L.`waza_id`) = 2;

Comments

[gottoteru]

IN を使用したらOR検索なのでwaza_idが1しかないものでヒットして期待したデータが取れませんでした
この場合、1と2と3が全て覚える事のできるモンスターのデータだけが欲しいです

[退会済みユーザー]

INSERTが出てきたので書き直しました。

[gottoteru]

ありがとうございます、大変申し訳なのですが書くのを忘れてました、取得条件が違うなどの理由でwaza_idが重複する事があるのですが、group byにwaza_idを加えれば期待した通りに動くでしょうか？

[退会済みユーザー]

WHERE L.`waza_id` IN (3, 3, 4) のようになるということですかね？

[gottoteru]

いえ、INSERT文で
INSERT INTO
.learn
(monster_id,waza_id)
VALUES
("5","1"),
("5","1");
このようなデータが作られています
これで1のカウントを取ったらカウントが2になることがあります

[退会済みユーザー]

本来そのようなデータができること自体がバグでは？

[gottoteru]

本当はもっと大量の情報があり、その中で大まかにレベルで覚える技・アイテムを使って覚える技・イベントの人物に教わる技などがあり仕様上一つのモンスターに対して同じ技が重複することがあります

[退会済みユーザー]

distinct つければいいんじゃないですかね？

[gottoteru]

ありがとうございます
無事データがとれました
countの中でdistinctを使えるとは知りませんでした
大変勉強になりました