SQL文の検索について

Question

SELECT * FROM hotels JOIN hotels_tags ON hotels.hotel_id = hotels_tags.hotel_id AND hotels.country_code = hotels_tags.country_code JOIN tags ON hotels_tags.tags_id = tags.tags_id

以下の三つのtabelをこのSQLで繋げます。
tagsに'朝食付き'と'キャンセル無料'がありますが、これをWHWER句で検索対象に指定した場合、上記の全て繋げたSQLに対して1つのホテルが
その両方のタグを持っている場合のみ取り出すというのはどうすれば良いですか。

SELECT * FROM hotels JOIN hotels_tags ON hotels.hotel_id = hotels_tags.hotel_id AND hotels.country_code = hotels_tags.country_code JOIN tags ON hotels_tags.tags_id = tags.tags_id
WHERE tags.tag = '朝食付き' AND tags.tag = 'キャンセル無料'

こうすると１行づつ見ていくので、タグが複数になった時点で、ヒットしませんし、ORにすると期待値とは異なります。どう検索をかければ良いですか。

CREATE TABLE `hotels` (
  `hotel_id` int(11) NOT NULL,
  `country_code` enum('JPN','PHL') DEFAULT NULL,
  `hotel_name` varchar(200) NOT NULL,
  `hotel_address` varchar(200) NOT NULL,
  `hotel_tel` varchar(100) NOT NULL,
  `price` int(11) NOT NULL,
  `likes` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `hotels` (`hotel_id`, `country_code`, `hotel_name`, `hotel_address`, `hotel_tel`, `price`, `likes`) VALUES
(1, 'PHL', 'クラウン リージェンシー ホテル & タワーズ (Crown Regency Hotel & Towers)', 'Osmeña Boulevard, セブ シティ, セブ, フィリピン', '63324389294', 30, '279'),
(2, 'PHL', 'サローサ インターナショナル ホテル & レジデンシャル スイーツ (Sarrosa International Hotel and Residential Suites)', 'Kasambagan, Mabolo, Cebu City, Philippines, セブ シティ, セブ, フィリピン, 6000', '12345678912', 22, '300'),
(3, 'PHL', 'マルコ ポーロ プラザ セブ ホテル (Marco Polo Plaza Cebu Hotel)', 'Cebu Veterans Drive, Nivel Hills, Apas, セブ シティ, セブ, フィリピン, 6000 - マップで立地をチェック', '12345678912', 20, '893'),
(4, 'PHL', 'Express Inn Cebu Mabolo', 'M.J. Cuenco Avenue corner Pope John Paul II, セブ シティ, セブ, フィリピン, 6000', '87634516783', 40, '0'),
(5, 'PHL', 'レッド プラネット セブ (Red Planet Cebu)', '36 Archbishop Reyes Avenue, セブ シティ, セブ, フィリピン', '98765432123', 6, '12'),
(6, 'PHL', 'リッチモンド プラザ ホテル (Richmond Plaza Hotel)', 'F. Sotto Drive Gorordo Avenue, セブ シティ, セブ, フィリピン', '49316491673', 35, '0'),
(7, 'PHL', 'ザ NS ロイヤル ホテル (The NS Royal Hotel)', 'Juana Osmeña Street, セブ シティ, セブ, フィリピン', '58732614881', 23, '11'),
(8, 'PHL', 'パーム グラス ホテル (Palm Grass Hotel)', '68 JUNQUERA STREET, セブ シティ, セブ, フィリピン', '47329864931', 12, '23'),
(9, 'PHL', 'ベストセラーウォーターフロント セブ シティ ホテル アンド カジノ (Waterfront Cebu City Hotel and Casino)', 'Salinas Drive Lahug, セブ シティ, セブ, フィリピン', '59457884901', 21, '13'),
(10, 'PHL', '\r\nドルシネア ホテル & スイーツ (Dulcinea Hotel and Suites)', 'Shangs Mactan Town Center, M.L Quezon National Highway Pajo, Lapu-Lapu City, マクタン島, セブ, フィリピン', '47153645137', 11, '33'),
(11, 'PHL', 'ゴールデン プリンス ホテル & スイート (Golden Prince Hotel & Suites)', 'Acacia Street Corner Archbishop Reyes Avenue, セブ シティ, セブ, フィリピン', '98312563651', 10, '12'),
(12, 'PHL', 'マンダリン プラザ ホテル (Mandarin Plaza Hotel)', 'Archbishop Reyes Corner Escario Street, セブ シティ, セブ, フィリピン', '64687645354', 36, '22');

ALTER TABLE `hotels`
  ADD PRIMARY KEY (`hotel_id`),
  ADD UNIQUE KEY `hotel_id` (`hotel_id`,`country_code`);

ALTER TABLE `hotels`
  MODIFY `hotel_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

CREATE TABLE `tags` (
  `tags_id` int(11) NOT NULL,
  `tag` varchar(100) NOT NULL,
  `tag_kind` enum('payment','service','equipment','internet','relaxation','language','location') DEFAULT NULL,
  `display` enum('1','0') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `tags` (`tags_id`, `tag`, `tag_kind`, `display`) VALUES
(1, '無料Wi-Fi', 'internet', '1'),
(2, '朝食付き', 'service', '1'),
(3, 'キャンセル無料', 'payment', '1'),
(4, '宿泊施設へ直接支払い', 'payment', '1'),
(5, '日本語対応', 'service', '0'),
(6, '無料送迎', 'service', '0'),
(7, '後日支払い可', 'payment', '1'),
(8, 'ツインベット', 'equipment', '0'),
(9, '禁煙', 'service', '0'),
(10, '24時間対応フロントデスク', 'service', '0'),
(11, 'スパ', 'relaxation', '0'),
(12, 'プール', 'relaxation', '0'),
(13, '全室無料Wi-Fi', 'internet', '0'),
(14, 'シャワー付き', 'equipment', '0'),
(15, 'テレビ放送', 'equipment', '0'),
(16, '冷蔵庫', 'equipment', '0'),
(17, 'ルームサービス', 'service', '0'),
(18, 'フィリピン語', 'language', '0'),
(19, '北京語', 'language', '0'),
(20, '英語', 'language', '0'),
(21, 'クレジットカード不要', 'payment', '1'),
(22, 'コンビニ', 'location', '0'),
(23, 'ランドリー', 'location', '0'),
(24, 'レストラン', 'location', '0'),
(25, 'デリバリーサービス', 'service', '0'),
(26, 'ホテル内レストラン', 'service', '0'),
(27, '洋服掛け', 'equipment', '0'),
(28, 'ペット可', 'service', '0');

ALTER TABLE `tags`
  ADD PRIMARY KEY (`tags_id`),
  ADD UNIQUE KEY `tag` (`tag`);

ALTER TABLE `tags`
  MODIFY `tags_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=29;

CREATE TABLE `hotels_tags` (
  `hotels_tags_id` int(11) NOT NULL,
  `hotel_id` int(11) NOT NULL,
  `country_code` enum('JPN','PHL') DEFAULT NULL,
  `tags_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO `hotels_tags` (`hotels_tags_id`, `hotel_id`, `country_code`, `tags_id`) VALUES
(24, 1, 'PHL', 1),
(41, 1, 'PHL', 2),
(42, 1, 'PHL', 3),
(25, 1, 'PHL', 4),
(43, 1, 'PHL', 5),
(44, 1, 'PHL', 6),
(26, 1, 'PHL', 7),
(27, 1, 'PHL', 8),
(28, 1, 'PHL', 9),
(29, 1, 'PHL', 10),
(45, 1, 'PHL', 11),
(46, 1, 'PHL', 12),
(51, 1, 'PHL', 13),
(30, 1, 'PHL', 14),
(31, 1, 'PHL', 15),
(32, 1, 'PHL', 16),
(52, 1, 'PHL', 18),
(53, 1, 'PHL', 19),
(54, 1, 'PHL', 20),
(97, 1, 'PHL', 27),
(33, 2, 'PHL', 1),
(34, 2, 'PHL', 3),
(35, 2, 'PHL', 5),
(36, 2, 'PHL', 8),
(37, 2, 'PHL', 10),
(38, 2, 'PHL', 13),
(39, 2, 'PHL', 16),
(40, 2, 'PHL', 17),
(93, 2, 'PHL', 22),
(1, 3, 'PHL', 1),
(2, 3, 'PHL', 2),
(3, 3, 'PHL', 3),
(4, 3, 'PHL', 4),
(6, 3, 'PHL', 5),
(7, 3, 'PHL', 6),
(5, 3, 'PHL', 7),
(8, 3, 'PHL', 8),
(9, 3, 'PHL', 9),
(10, 3, 'PHL', 10),
(11, 3, 'PHL', 11),
(12, 3, 'PHL', 12),
(13, 3, 'PHL', 13),
(14, 3, 'PHL', 14),
(15, 3, 'PHL', 15),
(22, 3, 'PHL', 16),
(23, 3, 'PHL', 17),
(95, 3, 'PHL', 21),
(96, 3, 'PHL', 27),
(94, 3, 'PHL', 28),
(55, 4, 'PHL', 1),
(56, 4, 'PHL', 3),
(57, 4, 'PHL', 5),
(58, 4, 'PHL', 6),
(73, 4, 'PHL', 7),
(59, 4, 'PHL', 8),
(60, 4, 'PHL', 9),
(61, 4, 'PHL', 10),
(62, 4, 'PHL', 11),
(65, 4, 'PHL', 12),
(66, 4, 'PHL', 13),
(67, 4, 'PHL', 14),
(68, 4, 'PHL', 15),
(69, 4, 'PHL', 16),
(70, 4, 'PHL', 17),
(71, 4, 'PHL', 18),
(72, 4, 'PHL', 20),
(74, 5, 'PHL', 1),
(75, 5, 'PHL', 3),
(76, 5, 'PHL', 6),
(77, 5, 'PHL', 7),
(78, 5, 'PHL', 8),
(79, 5, 'PHL', 9),
(80, 5, 'PHL', 10),
(81, 5, 'PHL', 13),
(82, 5, 'PHL', 14),
(83, 5, 'PHL', 15),
(84, 5, 'PHL', 17),
(85, 5, 'PHL', 18),
(86, 5, 'PHL', 20),
(87, 5, 'PHL', 21),
(88, 5, 'PHL', 22),
(89, 5, 'PHL', 24),
(90, 5, 'PHL', 26),
(98, 6, 'PHL', 1),
(99, 6, 'PHL', 2),
(100, 6, 'PHL', 3),
(101, 6, 'PHL', 4),
(102, 6, 'PHL', 5),
(103, 6, 'PHL', 8),
(104, 6, 'PHL', 9),
(105, 6, 'PHL', 10),
(106, 6, 'PHL', 13),
(107, 6, 'PHL', 14),
(108, 6, 'PHL', 15),
(109, 6, 'PHL', 16),
(110, 6, 'PHL', 17),
(111, 6, 'PHL', 18),
(112, 6, 'PHL', 19),
(113, 6, 'PHL', 20),
(114, 6, 'PHL', 22),
(115, 6, 'PHL', 23),
(116, 6, 'PHL', 24),
(117, 6, 'PHL', 25),
(118, 6, 'PHL', 26),
(119, 6, 'PHL', 27);

ALTER TABLE `hotels_tags`
  ADD PRIMARY KEY (`hotels_tags_id`),
  ADD UNIQUE KEY `hotel_id` (`hotel_id`,`country_code`,`tags_id`);

ALTER TABLE `hotels_tags`
  MODIFY `hotels_tags_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=255;

Comments

[Orlofsky]

質問に載せる内容は、聞きたいことを説明できる最低限の情報に絞っては？
雑音が多過ぎ。

[m.ts10806]

tabelって何でしょ

[sazi]

@Orlofskyさん
雑音ってどの情報ですか？

[Orlofsky]

saziさんって、確信犯でしょうか？
質問のSELECT文を実行できる必要最小限のカラムに絞らないと質問者は問題点をきちんと把握できないでしょう。無駄にデータが多いのも質問者が問題を理解する妨げにしかなっていないかと。

[sazi]

雑音というのは、直接関係のないデータという事ですか。
必要なカラムはselet文で分かりますから、質問に記載するスクリプトについては、コピペして流すだけですし、過剰と言われる部分は気にはなりませんでした。
よくコメントされている、create table とデータについて、質問者は記載しているのに、それで雑音と言われたら混乱してしまうと思ったんですよ。

Answer 1

選択したtagをパラメータとして渡しやすくなるようにしてみました。
指定したtagを集計した場合に、そのtagの数が指定したtagの数と一致するものがand条件と同じになります。

SELECT * FROM hotels
where (hotel_id, country_code) in (
          select hotel_id, country_code 
          from  hotels_tags inner join tags 
                on hotels_tags.tags_id = tags.tags_id
          where tag in ('朝食付き', 'キャンセル無料')
          group by hotel_id, country_code
          having count(*)=2
      )

count(*)と比較している数を、条件のtagの数と合わせる必要があります。

追記

hotelsのプライマリーはhotel_idなので、訂正。
※定義でなんで(hotel_id, country_code)をユニークにしているのか不明ですが。
前者と結果は変わらないと思いますが、簡潔なので。

SELECT * FROM hotels
where hotel_id in (
          select hotel_id 
          from  hotels_tags inner join tags 
                on hotels_tags.tags_id = tags.tags_id
          where tag in ('朝食付き', 'キャンセル無料')
          group by hotel_id
          having count(*)=2
      )

Comments

[momon-ga]

優しい。

[sazi]

momon-gaさんのをそのままんま展開した形ですねｗ

[sazi]

甘いでしょうか。成長されている印象ですけど。

[m.ts10806]

この内容理解して使うのならいいんですが。

[sazi]

少なくともこの質問はやることやって躓いてると見受けられますし、回答をどう活かすかは質問者マターと考えています。

Answer 2

関係演算の除算の問題です。IPAのデータベーススペシャリストの試験問題を調べてください。様々なSQLが書けるそうですが、紹介するのは not exists を2回使うやりかたです。

select hotel_id from hotels a where not exists(
  select 1 from tags c where c.tag in ('朝食付き','キャンセル無料') and not exists(
    select 1 from hotels_tags b where c.tags_id = b.tags_id and b.hotel_id = a.hotel_id));

Comments

[sazi]

シンプルですね。
条件としての変動要素が一つだけになるのでこちらの方が使い勝手は良いですね。

[退会済みユーザー]

おっしゃるとおり、はじめcount(*)=2を試しましたが、2も変動要素になることがわかり途中で除算に変更 しました。c.tag in ('朝食付き','キャンセル無料') といった選択条件をビューにして外に出せば教科書どおりになります。
それより、関係演算のひとつである除算の応用例が見つかったのが嬉しいですね。

[sazi]

後は速度面ですね。
質問のデータ程度では違いは無さそうですけど、大量になるとどうかというところ。

[退会済みユーザー]

相関副問い合わせは遅いですから。除算の課題は効率化（他の方法の探求）です。

Answer 3

結果としてどう見せたいのかによります。
命題を見る限りこうだと思います

select * from hotels as t1
inner join hotels_tags as t2 on t1.hotel_id = t2.hotel_id and t1.country_code = t2.country_code 
inner join tags as t3 on t2.tags_id = t3.tags_id
where t1.hotel_id in(
select hotel_id from hotels_tags as t4
where exists (select 1 from tags where tag IN('朝食付き','キャンセル無料') and tags_id=t4.tags_id)
group by hotel_id
having count(distinct tags_id)=2
)

しかしこれだと同じホテルが複数行に渡って表示されるので微妙
group_concat使ったほうがよいかも

select 
t1.hotel_id
,t1.country_code
,t1.hotel_id
,t1.hotel_name
,t1.hotel_address
,t1.hotel_tel
,t1.price
,t1.likes
,group_concat(t3.tag) as tags
from hotels as t1
inner join hotels_tags as t2 on t1.hotel_id = t2.hotel_id and t1.country_code = t2.country_code 
inner join tags as t3 on t2.tags_id = t3.tags_id
where t1.hotel_id in(
select hotel_id from hotels_tags as t4
where exists (select 1 from tags where tag IN('朝食付き','キャンセル無料') and tags_id=t4.tags_id)
group by hotel_id
having count(distinct tags_id)=2
)
group by hotel_id

Answer 4

あんまりしっかり見てないので、ダメかもしれんが。
joinした結果のtagをor検索すれば、ヒットするのは、１件か２件なのでしょ？

で、having使って２件あるのが、望むホテルじゃないかな？