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 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