On 04/01/2012, at 8:06 PM, E3 wrote:
> I've a SQLite db containing data about movies.
Implementing your description into a schema:
create table "movies"
( movie_id integer primary key not null
, title text collate nocase not null
, year integer
)
;
create table "actors"
( actor_id integer primary key not null
, name text collate nocase
, surname text collate nocase
)
;
create table "cast"
( cast_id integer primary key not null
, movie_id integer not null references "movies" (movie_id) on delete
cascade on update cascade
, actor_id integer not null references "actors" (actor_id) on delete
cascade on update cascade
, character_name text collate nocase --- the name of the character, eg.
"Mrs Doubtfire"
)
;
> In a single query I should retrieve all the characters of a given movie (at
> the application level I've the id of the current movie to start), name and
> surname of the actor and a list of the movies (!= this current movie) where
> the actor had a role:
>
> Character_name | Actor | Other movies where we've seen
> this actor
> Mrs Doubtfire | Robin Williams | Mork & Mindy, Dead Poets
> Society, ...
> Other name | Other actor | Related movies,...
>
> Is this possible? How?
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 = ?
Tom
Tom Brodhurst-Hill
BareFeetWare
--
iPhone/iPad/iPod and Mac software development, specialising in databases
[email protected]
--
Twitter: http://twitter.com/barefeetware/
Facebook: http://www.facebook.com/BareFeetWare
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
