Petite Abeille <petite.abei...@gmail.com> wrote:
> On Jan 4, 2012, at 12:50 PM, BareFeetWare wrote:
>
>> Yes. This works with the above schema:
>>
>> 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
Just change the last two joins to LEFT JOIN.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
