SQLiteで副問い合わせ

SQlite3を使っています。思ったような結果が出せないのでお願いします。
特定の俳優（A）と共演したことのある俳優の名前をダブりなく出したい。
その結果には特定の俳優Aの名前は含まない結果を出したい。
特定の俳優は「Art Garfunkel」です。
・movie表：
id ：　映画のID
title ：映画名
yr ：　公開年
director: 監督
budget ：予算
gross ：興行収入
・actor表：
id：俳優・女優のID
name ：俳優・女優の名前
・casting表：
movieid： movie表のidへの外部キー
actorid： actor表のidへの外部キー
ord ：俳優の出演表中の順位（主演1，助演2）

一行目で表示させるものをdistinctでnameダブらないようにactor表から検索する、ただし条件として「共演していた」事が必要になるのでwhereで条件指定
3つの表の共通項を利用して結合させてってやってみたのですが、結果としては検出される俳優の名前が圧倒的に少ない。

select distinct name from actor
where movieid in
(
select m.id from movie m
join actor a
on(c.actorid = a.id)
join casting c
on(c.movieid = m.id)
and a.name = 'Art Garfunkel'
);

以下を実行するとArt Garfunkelの出演作品の映画IDの一覧は出るのでこれにこだわって試行錯誤してるから駄目なんでしょうか？

select m.id from movie m
join casting c
on(c.movieid = m.id)
join actor a
on(c.actorid = a.id)
where a.name = 'Art Garfunkel';

gentaro

2021/07/02 06:44

actorテーブルにmovieidって項目ないのに主表のwhereで指定してるのは何で？

yambejp

2021/07/02 07:22

SQliteのご質問ならMySQLタグは外したほうがよいでしょう

行動規範の内容に同意します

回答1件

ベストアンサー

こんな感じで。

SQL
1select distinct a.name
2from  casting c
3      inner join actor a
4      on c.actorid = a.id
5where c.movieid in (
6        select movieid 
7        from  casting c 
8              inner join actor a
9              on c.actorid = a.id
10        where a.name = 'Art Garfunkel'
11      )
12  and a.name != 'Art Garfunkel'

一箇所にできるならやりたいと思うのが1つ。

取り敢えずは、With式

SQL
1with base as (
2  select c.movieid, a.name
3       , case when a.name = 'Art Garfunkel' then 1 else 0 end target 
4  from  casting c 
5        inner join actor a
6        on c.actorid = a.id
7)
8select distinct name
9from  base
10where movieid in (
11        select movieid 
12        from  base 
13        where target = 1
14      )
15  and target = 0