SQL文 特定の数値を持つ重複した値を取得したい

Question

table
post_id | category_id
1 | 4
2 | 2
3 | 1
4 | 2
5 | 3
1 | 3
2 | 6
3 | 3
4 | 4
5 | 4

上記のようなテーブル構成になっております。
ここからcategory_idが 3 と 4 を持つpost_idを取得したい。

上記の場合はpost_idが1 5を抽出したいです。

select post_id from table where category_id = 3 and category_id = 4;
とやると何も取得できず、原因はcategory_idが1つの値しかもっていないからだと思うのですが、こう言った場合どのように書けばいいのかご教授ください。

Answer 1

ミックさんのCASE式のススメ
http://www.geocities.jp/mickindex/database/db_case.html
を見ながら、Oracle12cで作ってみました :-)

with t(post_id,category_id) as(
select 1,4 from dual union
select 2,2 from dual union
select 3,1 from dual union
select 4,2 from dual union
select 5,3 from dual union
select 1,3 from dual union
select 2,6 from dual union
select 3,3 from dual union
select 4,4 from dual union
select 5,4 from dual)
select post_id
  from t
group by post_id
having sum(case category_id when 3 then 1 else 0 end) >=1
   and sum(case category_id when 4 then 1 else 0 end) >=1
order by post_id;

  POST_ID
---------
        1
        5

Comments

[Hello]

ご回答ありがとうございます。
勉強させていただきます。

Answer 2

既に終わっていますが、INTERSECTを使う方法もあります。

SELECT
  post_id
FROM
  table1
WHERE
  category_id=3
INTERSECT
SELECT
  post_id
FROM
  table1
WHERE
  category_id=4

SQL Fiddleで動くサンプル

Comments

[Hello]

algさん

ご回答ありがとうございます。
みなさまからご丁寧に回答いただいているにも関わらず、結果が得られたからといって早急に終わらせてしまいました。

勉強させていただきます。

[PineMatsu]

INTERSECT(積集合)か、データーベースらしくていいですね。SQLiteでも使えるんですね。

Answer 3

category_id=3 の結果と category_id=4 の結果を内部結合することで希望の結果が取得できそうです。

未検証ですが以下のようなクエリはいかがでしょうか。
（文法ミスがあるかもしれませんので、考え方を参考にしてみてください。）

select X.post_id, X.category_id, Y.category_id
from
  (select * from table where category_id = 3) AS X
inner join
  (select * from table where category_id = 4) AS Y
on
  X.post_id = Y.post_id

Comments

[Hello]

ありがとうございます！
思い通りの結果が返ってきました。

Answer 4

とりあえず質問に書かれているテーブルだと正常な結果を得られました。

select distinct(post_id) from table where post_id in (select distinct(post_id) from table where category_id=3) and category_id=4;

Comments

[fuzzball]

すでに終わってたｗ

[Hello]

fuzzballさん

ご回答ありがとうございます。
期待した結果が得られたからといって、早急に閉じてしまって追求しない。。。初心者の悪い癖ですねw

勉強させていただきます。
誠にありがとうございます。

Answer 5

JOINを使って同じテーブルを突き合わせて判断すればできます。

SELECT t1.post_id
FROM table t1
LEFT OUTER JOIN table t2 ON t1.post_id = t2.post_id
WHERE t1.category_id = 3 AND t2.category_id = 4;

同じテーブル同士なのでOR文やDISTINCTは不要でしたね。

Comments

[PineMatsu]

終わってたか・・・ww
でも、OUTER JOINを使ったサンプルということで（笑）

[Hello]

PineMatsuさん

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

1つの要件でも様々なやり方があるのですね。
勉強させていただきます。

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

Answer 6

上記のSQLでは「category_idが3かつ4のレコード」を抽出していますが、それだと該当するレコードがないので、「category_idが3または4のレコード」という意味で、以下で行けるのではないでしょうか。
※post_idは一意にしています。

select distinct post_id from table where (category_id = 3 or category_id = 4);

Comments

[Hello]

ご回答ありがとうございます。
いただいた方法ですと or 条件になり、今回の要件に当てはまらないんです。。。

テーブル構成が良くないですよね〜。何となく取れないのではないかと感じでおります。。
ご回答いただきまして誠にありがとうございます。

Answer 7

category_idが 3 と 4 を持つpost_idを取得
だけであれば
select * from table where category_id = 3 or category_id = 4

さらにpost_id が１と５のデータを抽出するのであれば

select * from table where (category_id = 3 or category_id = 4) and (post_id = 1 or post_id = 5)
でよいのではないでしょうか。

Comments

[Hello]

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

教えていただいたやり方ですと、数値が取得できませんでした。
select * from table where category_id = 3 or category_id = 4

おそらく
table
post_id | category_id
1 | 3 4
2 | 1 2
3 | 3 4

といったようにcategory_idに複数数値がないといけないのではないかと思っております。