On 05/01/2012, at 1:48 AM, Petite Abeille wrote:
>> select
>> "cast".character_name as "Character Name"
>> , "actors".name || ' ' || actors.surname as "Actor"
>> , group_concat("other movies".title, ', ') as "Other movies where we've
>> seen this actor"
>> from "movies"
>> join "cast" using (movie_id)
>> join "actors" using (actor_id)
>> join "cast" as "other cast" on "other cast".actor_id =
>> "actors".actor_id
>> join "movies" as "other movies" on "other movies".movie_id = "other
>> cast".movie_id
>> where "other movies".movie_id != "movies".movie_id
>> and "movies".movie_id = ?
>
> Unfortunately, looks like this query will filter out actors that have
> performed in only that one movie, as the inner join to "other cast" will not
> match anything. Perhaps a left outer join would be more appropriate, no?
Good catch. You're right. I should have used left joins so as not to filter
them out. That makes the query:
select
"cast".character_name as "Character Name"
, "actors".name || ' ' || actors.surname as "Actor"
, group_concat("other movies".title, ', ') as "Other movies where we've
seen this actor"
from "movies"
join "cast" using (movie_id)
join "actors" using (actor_id)
left join "cast" as "other cast" on "other cast".actor_id =
"actors".actor_id
left join "movies" as "other movies" on "other movies".movie_id =
"other cast".movie_id
where "other movies".movie_id != "movies".movie_id
and "movies".movie_id = ?
Thanks,
Tom
Tom Brodhurst-Hill
BareFeetWare
--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Twitter: http://twitter.com/barefeetware/
Facebook: http://www.facebook.com/BareFeetWare
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
