mysqlでテーブルに二つのIDを保持しているIDを取得する方法

Question

下記のテーブルがあるとします。

table

product_id｜category_id
1　　　　　｜1
1　　　　　｜2
2　　　　　｜1
2　　　　　｜4
3　　　　　｜2
3 　　　　 ｜4
4　　　　　｜1
4　　　　　｜2

上記テーブルで、category_idが1と２であるproduct_idを取得するsqlが知りたいです。
上記例であれば１と４が取得するにはどのようなsql文を記載すべきなのでしょうか。

Answer 1

こうですね

select product_id from tbl
where category_id in (1,2)
group by product_id
having count(distinct category_id)=2

sample 追記

以下で試してみるとよいでしょう。

create table tbl(product_id int,category_id int);
insert into tbl values(1,1),(1,2),(2,1),(2,4),(3,2),(3,4),(4,1),(4,2),(1,2),(5,1),(5,1); 

• product_id=1はcategory_id、1,2,2を持ち通常にカウントすると3ですがヒットします
• product_id=5はcategory_id、1,1を持ち2を持ちません、通常にカウントすると2ですがヒットしません

Comments

[sazi]

category_idとproduct_idでユニークであるならですね。
ユニークでない場合まで考慮したら、もう1階層ネストが必要。

[yambejp]

いや、あってませんか？

[sazi]

サンプルで見るならあっています。
でも、キー情報の提示が無いし、キーは別にあるテーブルのそのカラムだけを抜粋したものかもしれないので、前提条件が必要かと。

[sazi]

あ、失礼しました。
count()内でdistinctしてましたね。
じゃあ、これが一番効率良さそうですね。

[yambejp]

saziさん、了解しました
一応sample追記しておきました

Answer 2

select distinct t1.product_id 
from `table` as t1 inner join `table` as t2
      on   t1.product_id=t2.product_id 
       and t1.category_id=1 and t2.category_id=2

または、

select distinct product_id from `table`
where product_id in (select product_id from `table` where category_id=1)
  and product_id in (select product_id from `table` where category_id=2)

それぞれ、distinct の代わりに group by でも可

Answer 3

下記のように取得できないでしょうか。

SELECT product_id FROM `table` GROUP BY product_id
HAVING GROUP_CONCAT(DISTINCT category_id ORDER BY category_id) = '1,2';

---

追記：
IDの組み合わせがユニークであるという前提は確かに記載されていませんでしたので、DISTINCT指定を追加しました。
主キーやユニークキーになっているなど各IDの組み合わせが一意に定まっているのあれば、DISTINCTは不要かと思います。