More JOIN operations/ja

このチュートリアルで join の理解を深める。データベースを3つのテーブルで構成する。 movie（映画） と actor（役者） と casting（出演） である。

テーブルの詳細は以下である。

movie 映画

id

title タイトル

yr 年

director 監督

budget 予算

gross 収益

actor 役者

id

name 名前

casting 出演

movieid

actorid

ord 出演掲載順序 1なら主演

※ id は movie.id と actor.id の2種類あるので注意。SQLではテーブル名(movieとactor)とフィールド名を . で接続して区別する。

※ movie.id = actor.id にはならない（映画のid と 役者のid なので 参照キーと外部キーの関係にはならない）

※ movie.id = casting.movieid や actor.id = casting.actorid の関係がテーブル間にある。

映画データベースの詳細は以下のリンクから表示。 More details about the database.

サマリー

1962の映画

1962 年の映画のリストを表示（ id と title を表示）

（ヒント） 実行するだけ。文法の確認。

SELECT id, title
 FROM movie
 WHERE yr=1962

SELECT id, title
 FROM movie
 WHERE yr=1962

「市民ケーン」の上映年は？

'Citizen Kane'の年を示す。

SELECT yr 
FROM movie 
WHERE title='Citizen Kane'

スタートレック映画

スタートレック（'Star Trek'）というシリーズ映画のリストを表示（ id title yr ）。 年の順に掲載。

SELECT id,title, yr FROM movie
 WHERE title LIKE 'Star Trek%'
 ORDER BY yr

グレン・クローズのid

女優 'Glenn Close' 　の id ナンバーは何ですか？

SELECT id FROM actor
  WHERE name= 'Glenn Close'

カサブランカのid

映画 'Casablanca' カサブランカの id は何ですか？

SELECT id 
FROM movie 
WHERE title='Casablanca'

ポイント解説

カサブランカの出演者リスト

映画カサブランカの出演リスト(name)を出力する。'Casablanca'

その映画に出演した役者のリスト

movieid=11768※を使う。 (または、これまでの問題で得た値を何でも使ってよい)

※訳者注 データベースエンジンがMySQLに設定されている場合の値。

SELECT name
  FROM casting, actor
  WHERE movieid=(SELECT id 
             FROM movie 
             WHERE title='Casablanca')
    AND actorid=actor.id

エイリアンの出演リスト

映画「エイリアン」'Alien'の出演者リストを表示。

SELECT name
  FROM movie, casting, actor
  WHERE title='Alien'
    AND movieid=movie.id
    AND actorid=actor.id

ハリソン＝フォード映画

'Harrison Ford'  ハリソン＝フォードが出演した映画のリストを表示する。    

SELECT title
  FROM movie, casting, actor
 WHERE name='Harrison Ford'
    AND movieid=movie.id
    AND actorid=actor.id

ハリソン＝フォード共演

'Harrison Ford'ハリソン＝フォードが出演した映画で、彼が主演していない(ord <> 1） のリストを表示。

[Note: ord は、映画の出演リスト順、1 が主演を意味する。]

SELECT title
  FROM movie, casting, actor
 WHERE name='Harrison Ford'
    AND movieid=movie.id
    AND actorid=actor.id
  AND ord<>1

1962映画の主演者

1962年の全映画を、そのタイトルと主演と併記してリスト表示。

SELECT title, name
  FROM movie, casting, actor
 WHERE yr=1962
    AND movieid=movie.id
    AND actorid=actor.id
    AND ord=1

---

より難しい問題

---

ジョン＝トラボルタが多忙の年

'John Travolta' ジョン＝トラボルタが最も忙しかった年はいつですか？ その年と出演した映画の本数を表示する。

彼が2本より多く出演した各年について表示する。

SELECT yr,COUNT(title) FROM
  movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
where name='John Travolta'
GROUP BY yr
HAVING COUNT(title)=(SELECT MAX(c) FROM
(SELECT yr,COUNT(title) AS c FROM
   movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
 where name='John Travolta'
 GROUP BY yr) AS t
)

SELECT yr,COUNT(title) FROM
  movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
where name='John Travolta'
GROUP BY yr
HAVING COUNT(title)=(SELECT MAX(c) FROM
(SELECT yr,COUNT(title) AS c FROM
movie JOIN casting ON movie.id=movieid 
JOIN actor   ON actorid=actor.id
 where name='John Travolta'
 GROUP BY yr) AS t
)

ジュリー＝アンドリューズ出演映画

'Julie Andrews' ジュリー＝アンドリューズが出演した映画について、主演した役者の名前を調べ、その全てについて タイトル と 主演 を表示する。

Julie Andrews は Little Miss Marker の1980 年のリメイクに主演したが、オリジナル(1934)ではしていない。

Title はユニークフィールドではない。サブクエリーでidのテーブルを作成する。

SELECT movieid FROM casting
WHERE actorid IN (
  SELECT id FROM actor
  WHERE name='Julie Andrews')

SELECT title, name
  FROM movie, casting, actor
  WHERE movieid=movie.id
    AND actorid=actor.id
    AND ord=1
    AND movieid IN
    (SELECT movieid FROM casting, actor
     WHERE actorid=actor.id
     AND name='Julie Andrews')

{{#ev:youtube|BcNIDK5qYx8|854x480}}

主演30本の役者達

少なくとも30タイトル以上に主演した役者の名前をアルファベット順に掲載。

SELECT name
    FROM casting JOIN actor
      ON  actorid = actor.id
    WHERE ord=1
    GROUP BY name
    HAVING COUNT(movieid)>=30

1978年の映画

1978年の映画を、出演者数が多い順に、タイトルを表示。

  SELECT title, COUNT(actorid)
  FROM casting,movie                
  WHERE yr=1978
        AND movieid=movie.id
  GROUP BY title
  ORDER BY 2 DESC,1 ASC

アート＝ガーファンクルと一緒に

'Art Garfunkel' アート＝ガーファンクルと一緒に仕事をした人々をすべて表示。

SELECT DISTINCT d.name
FROM actor d JOIN casting a ON (a.actorid=d.id)
   JOIN casting b on (a.movieid=b.movieid)
   JOIN actor c on (b.actorid=c.id 
                and c.name='Art Garfunkel')
  WHERE d.id!=c.id

Clear your results

JOIN Quiz 2

Using_Null/ja これで確実に十分。外部結合(OUTER JOIN)に関する次のチュートリアルを見る状況ですよね。