SQL文の書き方について

Question

PostgreSQLのサンプルデータベースを使ってSQL文を勉強しています。
ER図はこうなっています。
![]

これで、例えば「特定ジャンルの映画名だけを抽出したい」、と言った場合、SQL文をどう書けば良いのか分からずに困っています。

categoryテーブルの中の「category_id」と「name」のカラムに映画ジャンルの情報があり、filmテーブルの中の「title」カラムに映画タイトルの情報が入っている事を確認しました。

この事から、
SELECT title FROM film JOIN film_category ON category_id=1;
とすればできるかな、と思ったんですが、一つのタイトルを延々抽出し続けるという結果になってしまいました。

このような場合、どう入力すれば特定ジャンルの映画名だけを抽出できるのでしょうか？

Comments

[m.ts10806]

>（超初学者です）
「初心者アイコン」をつけるのにとどめてください。
要件とは別の話なので、タイトルや本文には不要です。

Answer 1

ER図を見るにはリレーションを理解しなければなりません。
質問のER図はIE記法で記述されています。
ジャンルの指定は名称で指定すると考えると、必要となる関係は、
fileとfilm_categoryの関係(1:0)と、film_categoryとcategoryの関係(0:1)ですね。

この関係の結合は**外部結合(outer Join)**になります。

SELECT *
FROM  film 
      left join film_category 
      ON    film.film_id=film_category.film_id
      right join category 
      ON    film_category.category_id = category.category_id

上記に対して、目的の条件を加味すると以下になります。

SELECT film.title
FROM  film 
      left join film_category 
      ON    film.film_id=film_category.film_id
      right join category 
      ON    film_category.category_id = category.category_id
WHERE category.name = 'カテゴリ名'

※外部結合の場合当然Nullが発生しますが、where条件での**=**による比較ではNullは対象外なので、必然的に除外されます。

Answer 2

まずJOINの使い方をググって調べてください。
普通それは２つのテーブルを結合する目的で利用します。

結合した結果に対してWHEREでカテゴリのIDなりで絞り込みをかければ要件は十分満たせるでしょう。

追記

なんか面倒なんでたぶんこれが答えだろうと思うの書いとくわ。

SELECT title
FROM film
JOIN film_category ON film.film_id=film_category.film_id
JOIN category ON film_category.category_id = category.category_id
WHERE category.name = '検索したいカテゴリの名前';

カテゴリのIDで検索するならcategoryテーブルまで結合する必要はないけど、名前で検索したいなら必要。

Answer 3

SELECT
    ca.name
FROM category ca
INNER JOIN film_category fc
ON ca.category_id = fc.category_id
where
    ca.category_id = 1;

Comments

[gentaro]

filmテーブルにcategory_idは定義されてないのでエラーになるでしょうね。

[Orlofsky]

おお、敗因は安易に質問のSELECTを信用したわたしの至らなさです。
質問者氏はER図よりCREATE TABLE文を提示してください。Markdownは覚えてね。

Answer 4

既に出ている回答で解決可能そうですけど、別の組み方で。
※未検証

with getCategoryFilmId as(
  select
    film_id
  from
    film_category
  where
    category_id = 1 
)
select 
  title
from 
  film 
join getCategoryFilmId on
  getCategoryFilmId.film_id=film.film_id

WITH問い合わせ（共通テーブル式）

---

--以下、若干勘違いしていた回答
ER図だけでは何とも言えず、テーブル定義次第なところもあるのですが、
必要なのはGROUP BY ですかね。

Comments

[gentaro]

> 必要なのはGROUP BY ですかね。
結果セットに目的とする映画タイトルの重複が存在する設計には見えないので要らないと思うけど、どういう想定なんだろう。

[m.ts10806]

「一つのタイトルを延々抽出し続けるという結果に」というところから「じゃあ重複データを絞り込んでしまえ」という発想からGROUP BYを出しました。
「どういうデータをもとにどういうデータを出したいのか」という具体的な部分と、テーブル定義はやはり必要ですね。

[gentaro]

テーブル定義というのが何を想定しているのかわかんないけど、ER図があればこのぐらいのクエリは書けると思うけどなぁ。
よほど常識はずれな定義になってりゃ別だけども、そんなケースを想定する方がどうかと思うし。

[m.ts10806]

あぁ、理解しました。
ちょっと別パターンで回答考えてみます。

[m.ts10806]

やはりfilmとfilm_categoryが1対多になるのでGROUP BY必要そうに感じました。

[m.ts10806]

いやごめんなさい。カテゴリ指定なら１件になるから不要ですね。