mysqlで2つのテーブルを使った（INNER JOIN)データの抽出方法

mysqlで２つのテーブルをINNER JOINしてデータを抽出したいのですが、うまくデータが抽出できないのでSQL文の書き方を教えてください。

「テーブルの構成」
[postテーブル]
id (int) auto_increment
name (varchar)


[categoryテーブル]
id (int) auto_increment
post_id(int)
name (varchar)



「入力されているデータ」
[postテーブル]
id| name
1 | 自由課題
2 | 夏休みの宿題
3 | 研究

[categoryテーブル]
id| post_id|name
1 | 1 |国語
2 | 1 |算数
3 | 1 |理科
4 | 2 |国語
5 | 2 |英語
6 | 2 |社会
7 | 3 |理科
8 | 3 |英語
9 | 3 |国語


「抽出したいデータ」

postテーブルとカテゴリーテーブルを
SELECT * FROM post INNER JOIN category ON post.id = category.post_id
のようにINNER JOINして、categoryテーブルのnameに国語と理科の両方が含まれるpostテーブルのidとnameを抽出したいです。
上記のデータだとpostテーブルの「id=1,name=自由課題」と「id=3,name=研究」という結果が得られれば良いです。

SELECT * FROM post INNER JOIN category ON post.id = category.post_id where category.name='理科'
というcategoryのnameを１つだけ指定した場合は抽出できるのですが、
SELECT * FROM post INNER JOIN category ON post.id = category.post_id where category.name='理科' and category.name='国語'
と指定した場合 データが抽出できず空の結果が返ってきてしまいます。


２つのテーブルのINNER JOINという問題以前に 一つのカラムで「国語」かつ「理科」というand検索の方法がわかっていないのが問題のような気がしています。

ご回答よろしくお願い致します。

No.2 ベストアンサー 回答者： yambejp 回答日時： 2015/12/10 13:30

＞2の意味は「国語」「理科」という2つの語句で検索しているので2という数字を指定していると理解してよろしいのでしょうか？

ちゃんと説明した方がよかったですね。
ご理解の通り、２科目で検索しているという意味の「2」です。

ついでに「 count(distinct c.name)」でdistinctを使っている理由は
仮にcategoryテーブルで10番目のデータが「10 | 3 |国語」と9番目とかぶるような場合
count(c.name)だけだと数値が3になるからです
ユニークなカウントをして数値と合致させる必要があります

この回答へのお礼

yambejp様

ご回答ありがとうございます。
「 count(distinct c.name)」の説明までしていただき感謝です。
ユニークなカウントに注意する必要があるんですね。
group by や having　はまだあまり使いなれていないので、今後勉強していきたいと思います。

今後ともよろしくお願い致します。

お礼日時：2015/12/10 14:08

No.1 回答者： yambejp 回答日時： 2015/12/10 12:39

select p.id,p.name from post as p inner join category as c on p.id = c.post_id

and c.name in ('国語','理科')
group by id
having count(distinct c.name)=2

この回答へのお礼

yambejp様

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

教えていただいたSQL文を実行したところ、求める結果を得ることができました。

SQLの４行目「having count(distinct c.name)=2」の2の意味は「国語」「理科」という2つの語句で検索しているので2という数字を指定していると理解してよろしいのでしょうか？
この数字の意味がわからないので教えていただければと思います。

よろしくお願い致します。

お礼日時：2015/12/10 13:15