多対多の関係を持つテーブルから、条件で絞り込みをするクエリを組み立てたい。

Question

前提

とある製品の検索サイトを作っています。
多対多の関係を持つ「製品テーブル」と「タグテーブル」と、その「中間テーブル」があります。

製品テーブル(products)

id	name
1	製品A
2	製品B

タグテーブル(tags)

id	name
1	タグA
2	タグB

中間テーブル(product_tag)

id	product_id	tag_id
1	1	1
2	1	2
3	2	1

実現したいこと

多対多の関係を持つ製品テーブルの中からタグA,B両方を持つ製品を検索したい。
→ このケースにおいては、製品Aのみの取得。

発生している問題

どのようなクエリを発行すれば、条件に合致するレコードが取得できるかが分からない。

試したこと

１.WHERE IN での検索

→ 製品A,Bの両方が取得されてしまう。

select
  *
from
  `products`
where
  exists (
    select
      *
    from
      `tags`
      inner join `product_tag` on `tags`.`id` = `product_tag`.`tag_id`
    where
      `products`.`id` = `product_tag`.`product_id`
      and `tags`.`id` in (1, 2)
  );

２. WHERE AND WHERE... での検索

→ 一件も商品が取得されない。

select
  *
from
  `products`
where
  exists (
    select
      *
    from
      `tags`
      inner join `product_tag` on `tags`.`id` = `product_tag`.`tag_id`
    where
      `products`.`id` = `product_tag`.`product_id`
      and `tags`.`id` = 1
      and `tags`.`id` = 2
  );

補足情報（FW/ツールのバージョンなど）

DB: MySQL 8.0

Answer 1

たとえばこう

create table product_tag(
id int primary key,
product_id int not null,
tag_id int not null,
unique(product_id,tag_id)
);
insert into product_tag values
(1,1,1),
(2,1,2),
(3,2,1),
(4,3,1),
(5,3,2),
(6,3,3),
(7,4,1),
(8,4,3);

select product_id from product_tag 
group by product_id
having sum(tag_id in(1,2))=2

• 1はヒット
• 2はNG
• 3はデータは3つあるが1,2を含むのでヒット
• 4はデータは2つあるが2を含まないのでNG

Comments

[wilkinson]

ご回答ありがとうございます。
ご回答いただいた内容でやりたかったことが実現できました。

元の質問の趣旨とは異なるのですが、そもそも今回のような実現したい内容にとって、このようなテーブル設計は適切だと思われますか？

[yambejp]

> このようなテーブル設計は適切

はい、不特定なタグを選択させる場合は中間テーブルを使うのが有効です。
決め打ちの場合は製品テーブルの中にカラムとして用意する場合もあります。

[wilkinson]

無事、問題が解決しました。
誠にありがとうございました。